Postgresql equivalent of DELETE JOIN when table has composite primary key-CodePudding

I have 2 tables with a linking table and need to delete a record from the linking table. I can't see a simple way to do this as my linking table doesn't have its own primary key field (it's a composite primary key of the two fields)

My tables:

roles
  id
  role_name

permissions
  id
  permission_name

roles_permissions
  role_id
  permission_id

I have a role_name and a permisson_name and want to delete the role_permissions row that matches this.

SQL server can do DELETE JOINS and then you just do a simple where clause across the tables but postgresql doesn't seem to support this.

Closest I can find is

DELETE FROM TABLE
WHERE id IN (SELECT ...)

This doesn't work for me as I don't have a single primary key field, i.e. both fields need to match the results of the query.

CodePudding user response：

Use the IN operator with multiple columns

DELETE FROM the_table
WHERE (id_1, id_2) IN (SELECT other_id_1, other_id_2 
                       FROM ...)