MySQL Delete rows subquery You can’t specify target table ‘XXX’ for update in FROM clause

Today my colleague and I have received a gift from MySQL.
Our goal was to delete some rows obtaining them using a subquery.

Here is the query:

DELETE ps_pack FROM ps_pack
WHERE id_product_pack IN (select id_product_item from ps_pack where id_product_pack = 148356904);

And here the response of MySQL
You can't specify target table 'ps_pack' for update in FROM clause

The query is pretty easy but MySQL doesn’t want to delete rows in a table that is used in a subquery.
Looking for a solution we came across a post with the solution (unfortunately I lost the link).

Don’t ask me why but wrapping the query with some useless steps I fooled MySQL:

DELETE  p.* FROM ps_pack p
WHERE p.id_product_pack IN (SELECT  id_product_item FROM (SELECT id_product_item FROM ps_pack where id_product_pack = 148356904) x);
Annunci

Informazioni su Andrea Regoli

Project Manager .Net Developer WPF WP7 Asp.Net c# javascript ajax SQL sharepoint
Questa voce è stata pubblicata in SQL e contrassegnata con , , . Contrassegna il permalink.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...