CODEWITHSUNDEEP
sql
user1001101
user1001101

Reputation: 172

How to delete a row when first row second column is same as second rows first column in sql

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

Upvotes: 1

Views: 406

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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

SQL Fiddle Demo

Upvotes: 1

phnkha
phnkha

Reputation: 7892

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

)

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7763

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

Upvotes: 0

Related Questions