Mysql delete with subquery

Question

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 = ?)

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.

Example of rows:

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

Please can you confirm if you just want to delete rows with the given id or if you have a more complicated requirement. — a'r, Dec 15 '11 at 22:40
@kris: Or you want to delete all rows that have same `picture` with the row having `id=?` — ypercubeᵀᴹ, Dec 15 '11 at 22:42
Doesn't the link (mentioned above): http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause answer your question? — ypercubeᵀᴹ, Dec 15 '11 at 22:45
Sorry I'm not familiar with Distinct, so it doesnt make much sense to me.. — Kristian, Dec 15 '11 at 22:50
The 2 answers by Cheekysoft there are exactly what zerkms and I answered here. — ypercubeᵀᴹ, Dec 15 '11 at 23:04

zerkms · Answer 1 · 2011-12-15T22:56:38.430

10

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

This cheat will fool mysql analyzer

edited Dec 15 '11 at 22:56

answered Dec 15 '11 at 22:46

zerkms

249,484
69
436
539

Atleast it get's me another kind of error: Every derived table must have its own alias :) – Kristian Dec 15 '11 at 22:56
@kris: so add an alias for nested query (updated query with `x` alias) – zerkms Dec 15 '11 at 22:57

score 4 · Accepted Answer · answered Dec 15 '11 at 22:58

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

score 1 · Answer 3 · answered Dec 15 '11 at 22:35

1

Your query has a loop in it. Why don't you just do

DELETE FROM product_pictures
WHERE id = ?

answered Dec 15 '11 at 22:35

jzila

724
4
11

Mysql delete with subquery

3 Answers3

Linked

Related