I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group.
I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been struggling with it.
Will it be something simple like this (not working)?
DELETE FROM link_group WHERE link_reply = NULL;
There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the
USINGclause. Which technique is more appropriate depends on the specific circumstances.
Bold emphasis mine. Using information that is not contained in another table is a tad bit tricky, but there are easy solutions. From the arsenal of standard techniques to ...
... a NOT EXISTS anti-semi-join is probably simplest and most efficient for DELETE:
DELETE FROM link_group lg
WHERE NOT EXISTS (
SELECT FROM link_reply lr
WHERE lr.which_group = lg.link_group_id
);
Assuming (since table definitions are not provided) link_group_id as column name for the primary key of link_group.
The technique @Mihai commented works as well (applied correctly):
DELETE FROM link_group lg
USING link_group lg1
LEFT JOIN link_reply lr ON lr.which_group = lg1.link_group_id
WHERE lg1.link_group_id = lg.link_group_id
AND lr.which_group IS NULL;
But since the table expression in the USING clause is joined to the target table (lg in the example) with a CROSS JOIN, you need another instance of the same table as stepping stone (lg1 in the example) for the LEFT JOIN, which is less elegant and typically slower.
DELETE FROM links_group USING links_group AS lg LEFT JOIN links_reply AS lr ON lg.col= lr.some_other_col WHERE links_reply.some_other_col IS NULL– Mihai Apr 03 '16 at 18:52