I want to remove duplicates based on below condition.
My table contains data like cross relation. Column 1 value exist in column 2 and vice versa.
sample table
id id1
-------------
1 2
2 1
3 4
4 3
5 6
6 5
7 8
8 7
I want to delete 1 row from first two rows, same from third and forth, same for fifth and sixth and so on..
Can anyone please help?
Like this way you are going to delete just the second row from each group of 2 rows:
CREATE TABLE [LIST_ID](
[ID] [NUMERIC](4, 0) NOT NULL,
[ID_1] [NUMERIC](4, 0) NOT NULL
);
INSERT INTO LIST_ID (ID, ID_1)
VALUES
(1, 2),
(2, 1),
(3, 4),
(4, 3),
(5, 6),
(6, 5);
WITH First_Row AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row_Number, *
FROM LIST_ID
)
DELETE FROM First_Row WHERE Row_Number % 2 ='0';
SELECT * FROM LIST_ID;
How about this:
DELETE
FROM myTable
WHERE id IN (
SELECT CASE WHEN id < id1 THEN id ELSE id1 END
FROM myTable
)
Where myTable is the sample table with data.
declare @t table (id1 int, id2 int)
insert into @t (id1, id2)
values
(1, 2),
(2, 1),
(2, 1),
(2, 1),
(3, 4),
(3, 4),
(5, 6),
(7, 8),
(7, 6),
(6, 7),
(5, 0)
delete t2
from @t t1
inner join @t t2 on t2.id1 = t1.id2 and t2.id2 = t1.id1
where t2.id1 > t1.id1
select * from @t order by 1, 2
declare @t table (id1 int, id2 int)
insert into @t (id1, id2)
values
(1, 2),
(2, 1),
(3, 4),
(4, 3),
(5, 6),
(6, 5),
(7, 8),
(8, 7)
;
;with a as (
select
row_number() over (order by id1) rn
,t.id1
,t.id2
from
@t t
)
delete t from
@t t
join (
select
a.id1
,a.id2
from
a a
where
exists(
select
*
from
a b
where
a.id2 = b.id1 and a.id1 = b.id2 and a.rn > b.rn
)
) c on t.id1 = c.id1 and t.id2 = c.id2
;
select * from @t;
/* OUTPUT
id1 id2
1 2
3 4
5 6
7 8
*/
It'll vary a little based on which row you want to keep, but if you really have simple duplicates as in your example, and every pair exists in both orders, this should do it:
DELETE FROM MyTable
WHERE ID > ID1
So what i could understand you want to delete the rows from table where id = id1.
delete from TableA as a
where exists(select 1 from TableA as b where a.id = b.id1)
