Обсуждение: Delete from a table with JOIN

Hi,

I'm trying to delete from a table based on a foreign key from another table.
I've tried this:
DELETE FROM a
FROM a LEFT JOIN b
ON a.b = b.id
WHERE b.foo = 100

Also tried with the second FROM dropped.
All I get is a parse error:
parser: parse error at or near "JOIN" at character 11, just after first
FROM a.

Any suggestions would be appreciated
Cheers

--
Noel Faux
Department of Biochemistry and Molecular Biology
Monash University
Clayton 3168
Victoria
Australia

Noel <noel.faux@med.monash.edu.au> writes:
> I've tried this:
> DELETE FROM a
> FROM a LEFT JOIN b
> ON a.b = b.id
> WHERE b.foo = 100

There is no such syntax in Postgres (as I would have thought would be
reasonably apparent from the DELETE reference page).

BTW, if you had been able to do the above, you would have quickly
regretted it, since it would delete *every* row in a.  Better think
twice about the use of LEFT JOIN here.

You might be able to do what you want with a sub-select in the WHERE
clause, along the lines of

    DELETE FROM a WHERE id IN
      (SELECT b.id FROM b WHERE b.foo = 100);

This IN syntax is not too efficient in PG releases preceding 7.4,
but should work fine as of 7.4.

            regards, tom lane