CODEWITHSUNDEEP
sqlpostgresqlsql-delete
orange
orange

Reputation: 8090

delete record from select by composite key

I've been able to come up with a query that selects those records that I want to delete, but now I am stuck at being able to convert this to a delete query. The problem is that a composite key describes the selected rows, so a normal delete from table where key in (select key...) won't work (at least not for Postgres).

Is there any way of getting this to work?

This is the select query:

SELECT t1.*, link.*, t2.* FROM tbl as t1
JOIN link on link.a = t1.link_id
JOIN tbl as t2
ON link.b = t2.link_id
WHERE t1.data = t2.data and 
t1.some_id = q2.some_id

tbl can be identified by some_id and link_id (and potentially a 3rd identifier in some cases which I didn't mention).

Upvotes: 2

Views: 2519

Answers (1)

orange
orange

Reputation: 8090

Ahh... I think I answered it (should have thought longer about it).

DELETE FROM tbl as t1 
WHERE EXISTS (
  SELECT 1 FROM link
  JOIN tbl as t2
    ON link.b = t2.link_id
  WHERE 
      link.a = t1.link_id 
  AND t1.data = t2.data 
  AND t1.some_id = t2.some_id
)

Upvotes: 3

Related Questions