I have the following MySQL query:
DELETE FROM catalogue
WHERE catalogue_id IN (
SELECT catalogue_id
FROM catalogue
WHERE (
product_id = (SELECT product_id FROM catalogue WHERE catalogue_id = '2290')
AND length_id = (SELECT length_id FROM catalogue WHERE catalogue_id = '2290')
AND g开发者_StackOverflowauge_id = (SELECT gauge_id FROM catalogue WHERE catalogue_id = '2290')
)
)
But when I attempt to execute I get the following error message:
You can't specify target table 'catalogue' for update in FROM clause
Could someone advise on where I'm going wrong?
Perform double nesting
DELETE FROM catalogue
WHERE catalogue_id IN (SELECT catalogue_id FROM (
SELECT catalogue_id
FROM catalogue
WHERE (
product_id = (SELECT product_id FROM catalogue WHERE catalogue_id = '2290')
AND length_id = (SELECT length_id FROM catalogue WHERE catalogue_id = '2290')
AND gauge_id = (SELECT gauge_id FROM catalogue WHERE catalogue_id = '2290')
)) x
)
It fools mysql
Or you can use temporary table:
CREATE TEMPORARY TABLE t AS
SELECT catalogue_id
FROM catalogue
WHERE (
product_id = (SELECT product_id FROM catalogue WHERE catalogue_id = '2290')
AND length_id = (SELECT length_id FROM catalogue WHERE catalogue_id = '2290')
AND gauge_id = (SELECT gauge_id FROM catalogue WHERE catalogue_id = '2290')
);
DELETE FROM catalogue WHERE catalogue_id IN (SELECT catalogue_id FROM t);
With your query you got You can't specify target table 'catalogue' for update in FROM clause
because you can't make select and update on the same table in one query.
精彩评论