CODEWITHSUNDEEP
mysql
user198729
user198729

Reputation: 63626

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

Upvotes: 0

Views: 2390

Answers (4)

ysth
ysth

Reputation: 98388

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.

Upvotes: 2

Will
Will

Reputation: 1591

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.

Upvotes: 1

Kenaniah
Kenaniah

Reputation: 5201

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

Upvotes: 1

M.J.
M.J.

Reputation: 16646

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.

Upvotes: -2

Related Questions