Mysql 删除子查询

2022-01-23 00:00:00 subquery mysql

可能重复:
SQL删除:无法指定目标表在 FROM 子句中更新

我正在尝试删除一些行，但目前没有成功.

I'm trying to delete some rows, but is currently not in success.

DELETE FROM product_pictures 
WHERE picture = (SELECT picture FROM product_pictures WHERE id = ?)

您不能在 FROM 子句中指定目标表 'product_pictures' 进行更新

You can't specify target table 'product_pictures' for update in FROM clause

我以前从未见过此错误消息，也无法找到一些有用的信息来说明我做错了什么.

I've never seen this error message before, nor has I been able to find some useful info about what I'm doing wrong.

行示例:

ID    Picture
19    picture-grey.jpg
20    picture-grey.jpg
21    picture-grey.jpg

推荐答案

DELETE a 
FROM product_pictures AS a
  JOIN product_pictures AS b
    ON b.picture = a.picture
WHERE b.id = ?

或:

DELETE a 
FROM product_pictures AS a
  JOIN 
    ( SELECT DISTINCT picture
      FROM product_pictures
      WHERE id = ?
    ) AS b
    ON b.picture = a.picture

相关文章