准备工作
create table mastertable (
id INT,
uname VARCHAR(32),
primary key (id)
)
create table slavetable (
id INT,
uname VARCHAR(32),
primary key (id)
)
两表关联更新
将表mastertable中与表slavetable的id相等的数据的uname字段更新为表slavetable的uname字段的值。
UPDATE mastertable m INNER JOIN slavetable s ON m.id = s.id SET m.uname = s.uname
表与内存中的数据关联更新
如果来源数据不是表slavetable,而是内存中的数据,
如:[{“id”:1, “uname”:“a”},{“id”:2, “uname”:“b”},{“id”:3, “uname”:“c”}]
则可以如下
UPDATE mastertable m INNER JOIN (
SELECT 1 as id, 'a' as uname
UNION ALL
SELECT 2 as id, 'b' as uname
UNION ALL
SELECT 3 as id, 'c' as uname
) s ON m.id = s.id SET m.uname = s.uname
注意事项
表关联只能用 INNER JOIN