How to update two tables in one statement?

Question

UPDATE table1, tmpList
  SET table1.ts = tmpList.ts_value
WHERE table1.id = tmpList.id

UPDATE table2, tmpList
  SET table2.ts = tmpList.ts_value
WHERE table2.id = tmpList.id

I'm using MySQL

Answer 1

no, this is not possible if you are using simple queries. I am not familiar with my sql but in oracle one cannot update two tables at a time.

Answer 2

Assuming every id appears in both tables (ideally only once):

update tmpList inner join table1 using (id) inner join table2 using (id)
    set table1.ts = tmpList.ts_value, table2.ts=tmpList.ts_value;

Update: simply using left joins instead of inner joins makes this work even for ids that are only in one table - the inapplicable set clause seems to just be skipped; I should have tried it earlier, apparently.

Answer 3

http://dev.mysql.com/doc/refman/5.0/en/update.html

Answer 4

Why do you need one statement? Are you worried about one completing and the other failing, leaving a half-updated mess?

Id that's the case, transactions are what you need. eg.

begin work;

UPDATE table1, tmpList
  SET table1.ts = tmpList.ts_value
WHERE table1.id = tmpList.id;

UPDATE table2, tmpList
  SET table2.ts = tmpList.ts_value
WHERE table2.id = tmpList.id;

commit work;

You should be able to run all of the above in a single command, but if not, it's safe to run them separately. The data won't be permanently saved unless the commit completes, which only happens if all the previous lines between the begin...commit succeed.