分析原因

在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;