Mysql 错误代码：1093 You can‘t specify target table ‘tb‘ for update in FROM clause

mysql使用子查询in 更新数据是，会报出一下问题：

错误代码： 1093
You can't specify target table 'tb' for update in FROM clause

错误的意思是：不能先select出同一表中的某些值，再update这个表(在同一语句中)；此问题仅在Mysql中出现，Oracle中无此问题。

1、原始问题Sql脚本

UPDATE
  t_calling tb
SET
  tb.redialFlag = 1
WHERE tb.batchNo IN
  (SELECT
    MAX(t.batchNo) AS batchNo
  FROM
    t_calling t
  WHERE t.createTime > '2018-09-01'
    AND t.batchNo LIKE '2018%'
  GROUP BY t.phoneNo
  HAVING COUNT(t.batchNo) > 1);

2、解决方案

方案一：

将select出的结果，通过中间表select一遍，如此就规避了该错误。

调整后，可执行的Sql语句如下：

UPDATE
  t_calling tb
SET
  tb.redialFlag = 1
WHERE tb.batchNo IN
  (SELECT
    tt.batchNo
  FROM
    (SELECT
      MAX(t.batchNo) AS batchNo
    FROM
      t_calling t
    WHERE t.createTime > '2018-09-01'
      AND t.batchNo LIKE '2018%'
    GROUP BY t.phoneNo
    HAVING COUNT(t.batchNo) > 1) tt);

方案二：变更为关联查询

UPDATE
  t_calling tb,
  (SELECT
    MAX(t.batchNo) AS batchNo
  FROM
    t_calling t
  WHERE t.createTime > '2018-09-01'
    AND t.batchNo LIKE '2018%'
  GROUP BY t.phoneNo
  HAVING COUNT(t.batchNo) > 1) tt
SET
  tb.redialFlag = 1
WHERE tb.batchNo = tt.batchNo

方案三：使用exists，是对方案一和二的优化，效率最高。

UPDATE
  t_calling tb
SET
  tb.redialFlag = 1
WHERE EXISTS
  (SELECT
    tt.batchNo
  FROM
    (SELECT
      MAX(t.batchNo) AS batchNo
    FROM
      t_calling t
    WHERE t.createTime > '2018-09-01'
      AND t.batchNo LIKE '2018%'
    GROUP BY t.phoneNo
    HAVING COUNT(t.batchNo) > 1) tt
  WHERE tb.batchNo = tt.batchNo);
 

3、推演

 delete table from in (select ...);

亦有此问题，处理方案同上。