MySQL基于多列值删除重复行报错：无法在FROM子句指定更新目标表

报错原因 #

你遇到的can't specify target table 'daily' for update in FROM clause是MySQL的特有限制：不允许在DELETE/UPDATE语句的子查询中直接引用正在操作的同一张表，避免读写冲突。

正确SQL写法 #

写法1：嵌套子查询（逻辑最直观，适合中小数据量） #

把子查询结果再套一层临时表，MySQL就会将内层聚合结果视为独立的临时数据集，不会判定为直接引用操作表：

DELETE FROM daily
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM daily
        GROUP BY `Date`, symbol
    ) AS temp_table
);

注：Date是SQL关键字，需要加反引号包裹避免语法报错。

写法2：关联删除（性能更高，适合大数据量） #

通过自连接匹配同组内id更小的重复行直接删除，执行效率远高于IN子查询：

DELETE t1 FROM daily t1
INNER JOIN daily t2
  ON t1.`Date` = t2.`Date` 
  AND t1.symbol = t2.symbol 
  AND t1.id < t2.id;

操作前校验建议 #

执行删除前先做校验，避免误删数据：

• 验证需要保留的行是否符合预期：

SELECT MAX(id) AS keep_id, `Date`, symbol, COUNT(*) AS duplicate_count
FROM daily
GROUP BY `Date`, symbol
HAVING duplicate_count > 1;

• 统计将要删除的行数，确认数量无误后再执行删除：

SELECT COUNT(*) AS will_delete_count FROM daily
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id FROM daily GROUP BY `Date`, symbol
    ) AS t
);

Python调用示例 #

以pymysql为例，执行删除后需要提交事务才会生效：

import pymysql

# 连接数据库
db_conn = pymysql.connect(
    host="你的数据库地址",
    user="你的用户名",
    password="你的密码",
    database="你的库名",
    charset="utf8mb4"
)
cursor = db_conn.cursor()

# 执行删除SQL
del_sql = """
DELETE FROM daily
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id
        FROM daily
        GROUP BY `Date`, symbol
    ) AS temp_table
)
"""
cursor.execute(del_sql)
# 提交事务
db_conn.commit()

# 关闭连接
cursor.close()
db_conn.close()

内容的提问来源于stack exchange，提问作者Seriously