Delete records where count of a related records in another table matches a condition-CodePudding

I have two tables with the following schema:

table1 (
    id UUID UNIQUE NOT NULL PRIMARY KEY,
    ...
);

table2 (
    id UUID UNIQUE NOT NULL PRIMARY KEY,
    table1_id UUID NOT NULL FOREIGN KEY REFERENCES table1(id),
    ...
);

I would like to delete all records in table 1 where the count of related records in table 2 (meaning those referencing table1 with a foreign key) equals 1. I'm not entirely sure how to do this.

Here is an invalid query I've made up that expresses what I want to do:

DELETE 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.table1_id 
WHERE COUNT t2.table1_id = 1;

I am getting a syntax error with the LEFT JOIN. I'm not certain how to join on a delete.

I am using PostgreSQL 15.

CodePudding user response：

A straightforward option is to filter with a correlated subquery that commutes the count of matching record in table2 for each row of table1:

delete from table1 t1
where 1 = (
    select count(*) from table2 t2 where t2.table1_id = t1.id
)

The query should take advantage of the underlying index of the foreign key relationship.