Page 1 of 1

Update common column across multiple tables

Posted: Tue Mar 22, 2011 12:25 pm
by MichaelR
Is there a way to combine the following queries into a single multi-table update, and if so, would it be faster? There are 24 tables with the 'user' column, and every one needs to be updated as follows (where 2 and 3 are dupes of 1 and so need to be merged). With 4,000 sets of dupes I have close to 100,000 queries to run as it's set now (with another 100,000 merging different tables for the same reason). It's taking a very long time to run on our test server. Thanks ahead of time.

Code: Select all

UPDATE table1 SET user = 1 WHERE user IN (2,3);
UPDATE table2 SET user = 1 WHERE user IN (2,3);
UPDATE table3 SET user = 1 WHERE user IN (2,3);
...

Re: Update common column across multiple tables

Posted: Fri Mar 25, 2011 10:54 am
by Kurby
You can update on joined tables. Have you tried:

Code: Select all

UPDATE
table1 t1 JOIN table2 t2 USING(user) JOIN table3 t3 USING(user)
SET user = 1

Re: Update common column across multiple tables

Posted: Fri Mar 25, 2011 1:28 pm
by MichaelR
Unfortunately this only updates the column in the first table. But then this will work:

Code: Select all

UPDATE t1 JOIN t2 USING (user) JOIN t3 USING (user) SET t1.user = 2, t2.user = 2, t3.user = 2
I'm not sure why I didn't consider this before. :?

And would this in fact be a better solution than updating each table individually?