2

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).

orange
  • 7,755
  • 14
  • 75
  • 139
  • This is not a pretty way to do it, but couldn't you concatenate the keys and the query results? E.g. `delete from table where key1 || key2 || key3 in (select t1.key1 || t2.key2 || t3.key3 from t1, t2, t3 where...)` (`||` is how you concatenate in Oracle; not sure what it is in Postgres) – Marc Nov 02 '15 at 04:12
  • The keys are numeric, so concatenating them would potentially remove wrong ones (think about `key1=11` and `key2=1` - this could also mean `key1=1` and `key2 = 11` as the concatenated version is 111). – orange Nov 02 '15 at 04:15
  • http://stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join has some really nice answers regarding deletion with joins and cte – zedfoxus Nov 02 '15 at 04:16

1 Answers1

3

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
)
orange
  • 7,755
  • 14
  • 75
  • 139