Geek asks and answers

Mysql delete with subquery

Possible duplicate:
SQL Delete: unable to specify target table for update in FROM clause

I am trying to delete multiple lines, but it is currently not working.

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

You cannot specify the target table 'product_pictures' for updating in the FROMproposal

I have never seen this error message before, and I have not been able to find useful information about what I am doing wrong.

Example lines:

ID    Picture
19    picture-grey.jpg
20    picture-grey.jpg
21    picture-grey.jpg
0
source share
3 answers
DELETE a 
FROM product_pictures AS a
  JOIN product_pictures AS b
    ON b.picture = a.picture
WHERE b.id = ?

or

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

This cheat will fool the mysql parser

+10
source

.

DELETE FROM product_pictures
WHERE id = ?
+1

More articles:


All Articles