Updating multiple tables in a single sql statement

Question

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 :).

Your syntax should also have worked -- once you removed the semicolon after 999. — Uncle Iroh
– Uncle Iroh, Commented Jul 3, 2013 at 17:08

David M · Accepted Answer · 2012-01-13 14:24:53Z

4

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.

answered Jan 13, 2012 at 14:24

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating multiple tables in a single sql statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related