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



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Noel <noel.faux (AT) med (DOT) monash.edu.au> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend