第一种:根据条件从第二张表获取数据并更新到第一张表
UPDATE table1
JOIN table2 ON table1.id = table2._id
SET table1.column1 = table2.value1, table1.column2 = table2.value2
WHERE condition;
第二种:抽取部分的第二张表数据
UPDATE table1
JOIN (SELECT id, value FROM table2 WHERE condition) AS subquery
ON table1.id = subquery.id
SET table1.column1 = subquery.value;
第三种:
UPDATE table1
SET column1 = value1,column2 = value2
WHERE id exist (select 1 from table2 on table1.id = table2.id)
update test a set a.tx=(select tx from test2 b where a.id =b.id ) where exist (select 1 from test3 c where c.id =a.id)
第四种:
UPDATE table1
SET column1 = value1,column2 = value2
WHERE id IN (SELECT id from table2)
Mysql 两表联动更新
于 2024-01-04 10:03:59 首次发布