REKiRi REKiRi -4 years ago 96
SQL Question

Replace values in MySQL colums from row x to row y

I'd need to replace post dates from wordpress post table.
There are >800.000 post entries with the same date because of a migration.

How can I replace the date by "from row x to row"?

For example:

  • row 1 - 10.000 should have date
    2013-01-02 09:20:10

  • row 10.001 - 20.000 should have date
    2013-02-05 12:30:21

and so on...

Or maybe replacing by post id?

I know there is a sql query to do this, but I can not remember which one and how to use it correctly.

Answer Source

try adding a LIMIT to the sql to update rows:

UPDATE {table} 
SET {datefield} = "{desired date}" 
WHERE {datefield} = "{bad date}" 
LIMIT 10000;

this will update 10000 rows at a time with a new date as desired, however it's not particularly picky about which ones get updated in which order, generally it will be in the database's internal order which is (roughly) chronological.

is there any other part of the data you can use to determine which records should be updated with which date?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download