Best way to delete duplicate rows in an intersection table based on foreign key column

Question

I'm looking for a way to delete dupicate rows from an intersection table based on a foreign key from one of the tables from the opposite ends of the intersection table. For the sake of clarity, I'll say based on a foreign key value from the intersection table on the left 'tableA'.

    CREATE TABLE tableA
    (
        link varchar(64) NOT NULL PRIMARY KEY,
        name varchar(64) NOT NULL
    )

    CREATE TABLE tableB
    (
        link varchar(64) NOT NULL PRIMARY KEY,
        name varchar(64) NOT NULL
    )

    CREATE TABLE tableA_AND_tableB--Intersection Table
    (
        link varchar(64) NOT NULL PRIMARY KEY,
        LtableA varchar(64) references tableA(link),
        LtableB varchar(64) references tableB(link)
    )

Basically, I want to delete all duplicate rows in the intersection table based on the 'LtableA' foreign key field. For instance: Say I have 20 duplicates of 'LtableA = id20140722' in 'tableA_AND_tableB', how do I go about deleting all the rows matching the value 'id20140722' in 'tableA_AND_tableB' without affecting anything else?

Hope my question makes sense.

Step 1 - determine how to tell the difference between a duplicate and the row you want to keep. — Dan Bracuk, Jul 22 '14 at 17:07
So if an id is in both tableA and tableB you want to delete both rows from both tables and leave nothing behind? — Vulcronos, Jul 22 '14 at 17:29

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

Delete from tableA_AND_tableB where LtableA = 'id20140722'

This will remove all rows from that table sepcifically with that ID. Alternatively you can see this question for something that will delete all duplicates. Though that answer will keep either the first or last duplicate.

edited May 23 '17 at 12:27

Community

1
1

answered Jul 22 '14 at 17:13

Jake

328
2
14

Not that great an answer, but upvoted to get his rep over 50 so he can post comments, which this should have been. – Dan Bracuk Jul 22 '14 at 17:48

score 0 · Answer 2 · answered Jul 22 '14 at 18:56

If you want to delete duplicates but still keep one distinct copy of each row:

WITH t AS (
  SELECT ROW_NUMBER() OVER(PARTITION BY LtableA, LtableB ORDER BY link) row_num
  FROM tableA_AND_tableB
)
DELETE
FROM t
WHERE row_num > 1
  AND LtableA = 'id20140722'

Best way to delete duplicate rows in an intersection table based on foreign key column

2 Answers2