CODEWITHSUNDEEP
mysql
ale
ale

Reputation: 11820

Updating multiple tables in a single sql statement

Simple SQL syntax question. I'm writing a script to move data from an old schema to a new one. I have to switch off the integrity constraints when performing the move so my CASCASEs don't work. I want to update multiple tables with a new value like so:

UPDATE table1, table2 
SET table1.customer_id = 999, table2.customer_id = 999;
WHERE table1.customer_id = 3
AND table2.customer_id = 3

what's the correct syntax though? Hopefully the above explains what I want to achieve? Thanks :).

Upvotes: 1

Views: 4550

Answers (1)

David M
David M

Reputation: 72870

Try this:

UPDATE table1
INNER JOIN table2 USING (customer_id)
SET table1.customer_id = 999, table2.customer_id = 999
WHERE table1.customer_id = 3

Never tried it with an update to the key columns like this, but this would work for other columns, so worth a try.

Upvotes: 4

Related Questions