Update common column across multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Update common column across multiple tables

Post 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);
...
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Update common column across multiple tables

Post 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
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: Update common column across multiple tables

Post 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?
Post Reply