SQL Tutorial: Easily Update Rows Based on Complex Joins

Comment 1 Standard

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';

Advertisements

One thought on “SQL Tutorial: Easily Update Rows Based on Complex Joins

  1. update cars as c set c.garage=(select g.id from garage as g where C.owner = G.owner ) ;
    that’s how to do that….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s