Home > Back-end >  SQL Delete rows if 2 columns equal
SQL Delete rows if 2 columns equal

Time:08-19

I have a table like this:

id column2 column3 column4
1 value1 value2 somevalue
2 value3 value4 somevalue
3 value5 value5 somevalue

I want to delete any rows in the table where column 2 is equal to column 3. My table is 3000 rows long, and I want to perform this for every row that has the same value in column 2 and column 3. in this example, the row with value5 in column 2 and 3 would be deleted.

ive tried

DELETE FROM exampleTable t1 
WHERE EXISTS (SELECT 1 from Table t2
              WHERE t1.column2= t2.column3
              AND t2.Id = t1.Id) 

with no success. any help would be great thank you

CodePudding user response:

Since it is in the same table, it can be done way easier:

DELETE FROM exampleTable WHERE column2 = column3

Fiddle

CodePudding user response:

Test this:

delete from yourTable t1 where (select count(*) from yourTable t2 where t2.id=t1.id group by t2.id) >=2

CodePudding user response:

Hi you missing table name in sub query, it must like

SELECT * FROM [1test] t2  WHERE t2.column3 = t2.column2

So it will delete 3rd row, bcz column 2 and column 3 have same value.

  •  Tags:  
  • sql
  • Related