How to update two tables in one statement?

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

From stackoverflow

• 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.

  No Refunds No Returns : MySql has a multi-table update ability. Coming soon to a major data base near you. Soon being 5+ years and/or major releases.
  From Mrityunjay

• 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.

  : This is basically what I want,but not sure if it works.

  ysth : Yes, it works, in the situation I describe.

  : What if some id doesn't appear in both tables?Will it damage the tables?
  From ysth

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

  From Kenaniah

• 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.

  From Will