SQL删除同表非最大revision_id行报错，如何修改查询？

原SQL语句报错的原因是MySQL不允许在DELETE/UPDATE操作的WHERE子句中直接引用要修改的目标表作为子查询的数据源。可以通过以下两种方式修改语句实现需求：

方法一：嵌套子查询为派生表 #

将获取最大revision_id的子查询包装成一个临时派生表，避免直接引用目标表：

DELETE FROM node_revision__body
WHERE entity_id = 4
  AND revision_id NOT IN (
    SELECT max_rev FROM (
      SELECT MAX(revision_id) AS max_rev
      FROM node_revision__body
      WHERE entity_id = 4
    ) AS temp_table
  )

方法二：使用JOIN关联临时表 #

通过JOIN方式关联包含最大revision_id的临时表，定位需要删除的行，这种方式在数据量较大时效率更优：

DELETE nrb
FROM node_revision__body nrb
JOIN (
  SELECT MAX(revision_id) AS max_rev
  FROM node_revision__body
  WHERE entity_id = 4
) AS temp 
ON nrb.entity_id = 4 
AND nrb.revision_id != temp.max_rev

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