Selecting lines to delete with an implicit join

Question

I have two tables with a many-to-one relation. I can select items in the "child" table with a criteria in the parent table using an implicit join:

select * from child,parent where child.parentid=parent.id and parent.name like 'foobar%'

Is there a better (more efficient, more elegant) way to delete children that a plain:

delete from child where child.parentid in (select id from parent where parent.name like 'foobar%')

This answer hints that I could do:

delete child from child,parent where child.parentid=parent.id and parent.name like 'foobar%'

But on PostgreSql (through SquirrelSQL) I get a syntax error... of course I have long table names so the actual request is more like:

delete c from child c, parent p where c.parentid=p.id and p.name like 'foobar%'

So is this supported by PG, or is there another way?

Bonus points: can I delete items from the two tables using:

delete from child,parent where child.parentid in (select id from parent where parent.name like 'foobar%')

[As documented in the manual](https://www.postgresql.org/docs/current/sql-delete.html) DELETE requires a `USING` when you want to join tables. Your linked answer is for SQL Server, not for Postgres — , Feb 05 '20 at 10:58

score 1 · Accepted Answer · answered Feb 05 '20 at 11:00

1

You can delete from both tables in one statement using a data modifying CTE:

with deleted_parent as (
  delete from parent
  where name like 'foobar%'
  returning id
)
delete from child
where id in (select id from deleted_parent)

answered Feb 05 '20 at 11:00

Thx. Also figured out the USING syntax for the single-table deletion. Will accept answer as soon as SO lets me... – xenoid Feb 05 '20 at 11:07

score 0 · Answer 2 · answered Feb 05 '20 at 11:06

I would suggest leveraging the powers of your database by creating a foreign key with the on delete cascade option:

alter table child
    add constraint child_parent_fk 
    foreign key (parentid) references parent(id)
    on delete cascade;

On top of enforcing data integrity across tables, this will manage the deletion in the child table when a parent is removed, so you can just do...

delete from parent where name like 'foobar%'

... and rest assured that children will be deleted accordingly.

score 0 · Answer 3 · answered Feb 05 '20 at 11:08

0

In postgres, syntax for what you are trying to do is using "USING"

delete from child C USING parent P where C.parentid=P.id 
and P.name like 'foobar%'

answered Feb 05 '20 at 11:08

cosmos

2,263
1
17
30

Selecting lines to delete with an implicit join

3 Answers3