SQL Tutorial: Easily Update Rows Based on Complex Joins
There are a lot of times I’d like to update hundreds of rows in a database based on a complex set of conditions. In doing a traditional update SQL statement there is no way to pull in joins and compare the values. My little cheat for this is to run a select statement that generates all the appropriate update statements. Then all you have to do is run the resulting SQL string.
For example: Lets say you have a car racing game. You added a new garage feature where members can sort their cars into the appropriate garage. By default you gave each member their own garage when you created the new feature. Now you want to update each car belonging to every member so that it is placed inside the appropriate garage. You could build something like the following.
select ("update cars set garage='" + G.id + "' where car_id='" + C.id + "';") as SQL from garage G
inner join cars C on C.owner = G.owner
This would give you results along the lines of:
update cars set garage='2' where car_id='3421';
update cars set garage='2341' where car_id='133212';
update cars set garage='4452'' where car_id='98321';
