SQL Delete rows if 2 columns equal

Question

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

Am I missing something? Did you try: DELETE FROM exampleTable WHERE column2= column3? — forpas
– forpas, Commented Aug 18, 2022 at 9:28

Kevin · Accepted Answer · 2022-08-18 09:31:59Z

4

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

DELETE FROM exampleTable WHERE column2 = column3

Fiddle

answered Aug 18, 2022 at 9:31

Kevin

1,2415 gold badges18 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

milo204 Over a year ago

thank you! i was clearly ovethinking it!

Aravind Aravind · Accepted Answer · 2022-08-18 09:38:40Z

1

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.

edited Aug 18, 2022 at 9:38

answered Aug 18, 2022 at 9:30

Aravind Aravind

1891 silver badge10 bronze badges

2 Comments

milo204 Over a year ago

thanks! for some reason i get the error ' invalid identifier 'T2.ID''

Aravind Aravind Over a year ago

as u say its same table, then directly can delete it.

Mohammed Abdelouahab · Accepted Answer · 2022-08-18 09:34:18Z

0

Test this:

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

answered Aug 18, 2022 at 9:34

Mohammed Abdelouahab

891 silver badge5 bronze badges

Collectives™ on Stack Overflow

SQL Delete rows if 2 columns equal

I have a table like this:

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I have a table like this:

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related