CODEWITHSUNDEEP
sqlsql-serversql-delete
Trellmor
Trellmor

Reputation: 674

Delete records from table where a = b and b = a

I have a table with two fields, a and b. Some records are duplicated in the sense that a = b and b = a. I want to delete on of these records.

Consider this:

declare @temp table (a int, b int)

insert into @temp values (1, 2)
insert into @temp values (3, 4)
insert into @temp values (4, 3)
insert into @temp values (5, 6)

--delete 3, 4 or 4, 3

select * from @temp

/*
a | b
--|--
1 | 2
3 | 4
5 | 6

or (I don't care which one)

a | b
--|--
1 | 2
4 | 3
5 | 6
*/

How can I accomplish this? It needs to support Microsoft SQL Server 2000 and up.

Upvotes: 2

Views: 1944

Answers (2)

HLGEM
HLGEM

Reputation: 96562

Here is a solution for newer version of SQL Server

declare @temp table (a int, b int)

insert into @temp values (1, 2)
insert into @temp values (3, 4)
insert into @temp values (4, 3)
insert into @temp values (5, 6)
insert into @temp values (6, 5)
--delete 3, 4 or 4, 3


delete t3
--select * 
from 
(select t1.a, t1.b,rank() over (partition by t2.a +t2.b order by t1.a) as row_number from @temp t1
join @temp t2 on t2.a = t1.b and t2.b = t1.a)c
join @temp t3 on c.a =t3.a and c.b = t3.b
where c.row_number <>1

select * from @temp

Posting just to show the newer syntax for anyone else who is searching for the same thing.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

DELETE  x
FROM    TableName x
        INNER JOIN
        (
          SELECT  a.A, a.B
          FROM    tableName a
                  INNER JOIN tableName b
                      ON ((a.A = b.A AND a.b = b.b) OR
                          (a.A = b.B AND a.b = b.A)) AND 
                         a.a > b.a
        ) y ON x.A = y.A AND x.B = y.B

Upvotes: 5

Related Questions