同一张表字段更新
- user 表中,把 id = 3 的记录的 username 更新为 id = 2 的记录的 username 的值。
- SQL 语句:
-
UPDATE user u1, user u2 SET u1.username = u2.username WHERE u1.id = 3 AND u2.id = 2;
-
多表之间字段更新
- 把 manager 表 id = 1 的记录的 name 字段设置为 user 表 id = 3 的记录的 username 字段的值,nickname 字段设置为 staff 表 id = 1 的记录的 name 字段的值。
- SQL 语句:
-
UPDATE user u, staff s, manager m SET m.name = u.username, m.nickname = s.name WHERE u.id = 3 AND s.id = 1 AND m.id = 1;
-
批量更新
UPDATE test_table
SET
update_time = NOW(),
status = CASE id
WHEN '1000001' THEN '1'
WHEN '1000002' THEN '2'
WHEN '1000003' THEN '3'
END,
remark = CASE id
WHEN '1000001' THEN '备注1'
WHEN '1000002' THEN '备注2'
WHEN '1000003' THEN '备注3'
END
WHERE
id IN (1000001 , 1000002, 1000003);
作用:批量更新以下记录:
[
{"id":1000001,"remark":"备注1","status":1},
{"id":1000001,"remark":"备注2","status":2},
{"id":1000001,"remark":"备注3","status":3}
]