How to delete a row if first row's code column equals to the second rows number column and second rows' code column equals to the first row's number column.
sql must not use loop
use this as sample code
CREATE TABLE #temp(id int, code int, Number int)
Insert into #temp values(1,2,1)
Insert into #temp values(2,1,2)
Insert into #temp values(3,2,3)
Insert into #temp values(4,2,3)
Insert into #temp values(5,2,3)
select * from #temp
Since you didn't specify what RDBMS you are using. Let me assume that it is SQL Server, then you can do this:
;WITH CTE
AS
(
select * , ROW_NUMBER() OVER(ORDER BY ID) rownum
from temp
), RowsToDelete
AS(
SELECT c1.*
FROM CTE c1
INNER JOIN CTE c2 ON c1.rownum - c2.rownum = 1 AND c1.number = c2.code
)
DELETE r
FROM temp r
WHERE ID IN ( SELECT ID
FROM RowsToDelete);
For the sample data, you posted this will delete only one row, which is:
ID CODE NUMBER
2 1 2
Simply use a join to get the rows you want to delete using the criteria you've specified:
delete t1
from #temp t1
inner join #temp t2
on t1.code = t2.Number and t2.code = t1.Number
delete t1
from #temp t1
where exists(
select * from #temp t2
where t1.code = t2.Number and t2.code = t1.Number and t2.Id > t1.Id
)
