How can I delete duplicate rows in table with 2 columns that flip flop data?

Viewed 22

I have the following table:

Claim#1 Claim#2
ABC 123
123 ABC
DEF 456
789 GHI

I need to figure out how to eliminate one of the first 2 rows since it is pulling the same data only switched.

3 Answers

You may use a least/greatest trick here:

SELECT DISTINCT
    CASE WHEN Claim1 < Claim2 THEN Claim1 ELSE Claim2 END AS Claim1,
    CASE WHEN Claim1 < Claim2 THEN Claim2 ELSE Claim1 END AS Claim2
FROM yourTable;

If your SQL database happen to support the scalar LEAST() and GREATEST() functions, then we can simplify to:

SELECT DISTINCT
    LEAST(Claim1, Claim2) AS Claim1,
    GREATEST(Claim1, Claim2) AS Claim2
FROM yourTable;
  • Selecting all (claim1,claim2) values that do not exist (c2.id is null) like (claim2,claim1)
  • And from the records that have a reversed match (not c2.id is null) only select those where claim1 < claim2
select c1.*
from claims c1
left join claims c2 on c2.claim1=c1.claim2 and c2.claim2=c1.claim1
where c2.id is null
  or c1.claim1<c1.claim2 and not c2.id is null

DBFIDDLE

I'm sure there are a few clever ways to do this, however I'm just going with the brute force way. Sure not optimized, but easy to understand and adjust as needed.

First we need to inner join to itself and get the values to remove, and then loop it and delete them one at a time.

IF OBJECT_ID('claims') IS NOT NULL
    DROP TABLE claims
GO

CREATE TABLE [dbo].[claims](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [claim1] [varchar](50) NULL,
    [claim2] [varchar](50) NULL,
 CONSTRAINT [PK_claims] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


INSERT INTO claims VALUES 
('ABC', '123'), 
('123', 'ABC'),
('DEF', '456'),
('456', '555'),
('789', 'GHI'),
('111', 'AAA'),
('AAA', '111')
GO

DECLARE @CONTINUE INT = 1
WHILE @CONTINUE <> 0
BEGIN
    DELETE TOP(1) FROM CLAIMS WHERE id IN (
    SELECT a.id FROM CLAIMS a
    JOIN CLAIMS b
    ON a.claim1 = b.claim2
    AND b.claim2 = a.claim1
    )
    SET @CONTINUE = @@ROWCOUNT
END
GO

SELECT * FROM claims
Related