MySQL删除与子查询 [英] Mysql delete with subquery

问题描述

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

Possible Duplicate:
SQL Delete: can't specify target table for update in FROM clause

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

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

这篇关于MySQL删除与子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！