CODEWITHSUNDEEP
sqlpostgresqljoinsql-delete
xenoid
xenoid

Reputation: 8869

Selecting lines to delete with an implicit join

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%')

Upvotes: 0

Views: 198

Answers (3)

cosmos
cosmos

Reputation: 2303

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%'

Upvotes: 0

GMB
GMB

Reputation: 222382

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.

Upvotes: 0

user330315
user330315

Reputation:

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)

Upvotes: 1

Related Questions