SQL Delete rows if 2 rows equal-CodePudding

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.