分析原因
在MySQL中,可能会遇到You can't specify target table '表名' for update in FROM clause这样的错误
它的意思是说,不能在同一语句中,先select出同一表中的某些值,再update这个表,即不能依据某字段值做判断再来更新某字段的值。
这个问题在MySQL官网中有提到解决方案:MySQL-UPDATE- 拉到文档下面
例如下面这张t_message表
+----+-----+-----------+---------------------+
| id | uid | content | addtime |
+----+-----+-----------+---------------------+
| 1 | 1 | content1 | 2022-04-26 00:00:01 |
| 2 | 2 | content2 | 2022-04-26 00:00:02 |
| 3 | 3 | content3 | 2022-04-26 00:00:03 |
| 4 | 1 | content4 | 2022-04-26 00:00:04 |
| 5 | 3 | content5 | 2022-04-26 00:00:05 |
| 6 | 2 | content6 | 2022-04-26 00:00:06 |
| 7 | 2 | content7 | 2022-04-26 00:00:07 |
| 8 | 4 | content8 | 2022-04-26 00:00:08 |
| 9 | 4 | content9 | 2022-04-26 00:00:09 |
| 10 | 1 | content10 | 2022-04-26 00:00:10 |
+----+-----+-----------+---------------------+
我想将每个用户第一条消息的内容更新为Hello World
UPDATE t_message SET content = 'Hello World' WHERE id IN ( SELECT min(id) FROM t_message GROUP BY uid ); -- 会出现如下错误 -- ERROR 1093: You can't specify target table 't_message' for update in FROM clause -- 解决方法:select 的结果再通过一个中间表 select 多一次,就可以避免这个错误 UPDATE message SET content = 'Hello World' WHERE id IN ( SELECT min_id FROM ( SELECT min(id) AS min_id FROM t_message GROUP BY uid ) AS a ); -- 更新的其他方式 update fa_info fb, ( select fb.member_id as oldMemberId, mc.id as newMemberCardId from fa_info fb inner join ott_member m on fb.member_id = m.id and m.is_deleted = 0 left join ott_card c on m.idcard_no = c.idcard_no and m.type = c.type and c.is_deleted = 0 inner join ott_member_card mc on mc.member_id = m.id and (mc.card_id = c.id or mc.card_id is null) and mc.is_deleted = 0 ) mm set fb.member_id = mm.newMemberCardId where mm.oldMemberId = fb.member_id;