Search code examples
sqlpostgresqlsql-delete

sql query Delete rows that belongs to a user in a many-many table PostgreSQL

I have 3 tables

dragons
id, name, age, creation_date

users
id, name, user, pass

users_dragons
user_id, dragon_id

The idea is to make a query that deletes rows from the dragon table that have a lower age than the given and that belongs to a given user using the many-many table. I was planning to use exist statement but I'm a little new on SQL and still don't fully understand.

Solution 
  • delete from dragons where id in (
select d.id from dragons d, users u, users_dragons ud where d.age < :YourGivenAge and ud.dragon_id = d.id and ud.user_id in (
select id from users where name = :YourGivenName ));

    And for cascade delete just store the id and first delete from users_dragons table where dragon_id = stored id