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
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.