You can't specify target table 'A' for update in FROM clause

Question

This is my request

> DELETE FROM A WHERE id in (    Select
> id From A    Left Join B on A.id=B.id 
> Where B.id is null )

When I execute this I have this error

You can't specify target table 'A' for update in FROM clause

possible duplicate of [SQL Delete: can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause) — Neil Knight, Apr 12 '11 at 08:23

score 5 · Accepted Answer · answered Apr 12 '11 at 08:28

5

Maybe you could do it like this instead?

DELETE FROM A WHERE id NOT IN (SELECT DISTINCT B.id FROM B);

answered Apr 12 '11 at 08:28

Niklas

score 1 · Answer 2 · answered Apr 12 '11 at 08:28

1

You want to delete all records from table A that don't have a matching id in table B?

How about this:

DELETE
FROM A
WHERE NOT EXISTS (select 1 from B where A.id = B.id);

answered Apr 12 '11 at 08:28

Sam Choukri

OK, nice to know. I thought you're not allowed to mention the outer tables at all in nested selects... – Lukas Eder Apr 12 '11 at 08:39

score 1 · Answer 3 · answered Apr 12 '11 at 08:31

1

DELETE FROM A
WHERE NOT EXISTS (
    SELECT *
    FROM B
    WHERE content_id = B.content_id
)

answered Apr 12 '11 at 08:31

Marco

3 Answers3