MySQL Update

[NZ_Kiwis]

I've found all rows in my database that our out of date - I've then looped though all these and displayed them on a form to be updated, how can I now loop though and update each of these rows?

for example how do I know what rows to update...

"UPDATE ABC SET THIS = THAT WHERE ID = ??"

[Res]

You're on the right track. You use the where clause in the update statement to limit the rows you are going to update exactly as you would use the where clause to limit the rows you are pulling in a select statement. You have a few options, you can update them based on the same logic you are using to pull the records.

ie;

$sql = "Update `table` SET `field` = 'value' WHERE `date` >= '2010-01-01' AND `date` <= '2010-10-01';

Or you can loop over each record and update them individually based on the primary key.

$sql = "Update `table` SET `field` = 'value' WHERE `id` = '".$id."'";

There WHERE has the same effect in an update as in a select, but you are instead selecting records to update rather then to pull. I'd recommend copying your table and playing around, as if you forget the WHERE portion you will update ALL the records 😉