Re: Delete from a table with JOIN

Noel <noel(dot)faux(at)med(dot)monash(dot)edu(dot)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