I have a database with several thousand rows where I want to update the date field.

I tested the following query on one row:

PHP Code:

UPDATE `database`.`tablename` SET `date` = '2009-10-11' WHERE `date` = '0000-00-00'  LIMIT 1 ; 


It worked fine.

Now my question is can I change just the limit on that statement and sequence though the database using different update dates until all the '0000-00-00' entries are replaced?

I really don't want them all to be the same and I don't really care which ones become newer or older as long as there is a range of dates.

I am doing this on localhost so server load is not a consideration.

why not just write a simple script to do it for you?

PHP Code:

$date = '2009-10-11';
$result = $mysql->query('select date from tablename where date="0000-00-00"');
$timestamp = strtotime($date);
while($result->num_rows) {
    $mysql->query('UPDATE `database`.`tablename` SET `date` = "'.$date.'" WHERE `date` = '0000-00-00'  LIMIT 1');
    $date = date('Y-m-d',$timestamp);
    $timestamp = strtotime('-1 day',$timestamp);
    $result->free();
    $result = $mysql->query('select date from tablename where date="0000-00-00"');
} 


Untested, but something like that should work handily.

I would and I could but I don't want them all to be the same. I want a more natural look to the dates.

Make it skip back randomly between 1-5 days each time.

PHP Code:

$amount = rand(1,5);
$timestamp = strtotime('-'.$amount.' day',$timestamp); 


For the benifit of anyone who discovers this thread in the future and wants to know how to do using phpMyAdmin, (yes I made a typo in the title), all you have to do is change the limit to whatever number of rows you want to update at one time.

If you have the show this query after completion box checked you will come back to the same page when it is complete. You can then repeat it on the same number of rows or simply change the date and go again.

It just isn't all that hard to do.

You really don't need a backend for a scipted site if you take the time to learn all the tools available in phpMyAdmin.