MySQL异常 #You can‘t specify target table ‘表名‘ for update in FROM clause

1.异常现象

在 MySQL 中，或在 Navicat 工具中，执行 SQL 会发生：

[Err] 1093 - You can't specify target table 't_order' for update in FROM clause

 

2.排查分析

在 MySQL 中，写 SQL 脚本从一个查询结果中 UPDATE 某值的时候 ，可能会遇到 You can't specify target table '表名' for update in FROM clause 这样的错误。这个意思是说，不能在同一语句中先 SELECT 出同一表中的某些值，再 UPDATE 这个表，即不能依据某字段值做判断再来更新某字段的值。

比如如下操作就不行：

UPDATE t_order SET total_count = 1 WHERE id IN (
  SELECT id FROM t_order WHERE total_count > 1 AND id NOT IN (
    SELECT order_id FROM t_order_detail GROUP BY order_id HAVING COUNT(order_id) >= 2
  )
);

执行脚本会发生：[Err] 1093 - You can't specify target table 't_order' for update in FROM clause

 

3.解决方案

使用中间表或临时结果，即将 SELECT 出的结果通过 TEMP 表，再 SELECT 一遍，这样就可以规避错误。

UPDATE t_order SET total_count = 1 WHERE id IN (
  SELECT temp.id FROM (
    SELECT id FROM t_order WHERE total_count > 1 AND id NOT IN (
      SELECT order_id FROM t_order_detail GROUP BY order_id HAVING COUNT(order_id) >= 2
    )
  ) temp
);

注：需要注意的是，这个问题只出现在 MySQL，MSSQL 和 Oracle 不会出现该问题。