Request to delete rows in which columns match or NULL

Question

It seems simple, but I could not find anything. Any help is appreciated.

I have a table with multiple columns. I need to compare several of these columns with each other and remove the rows in which they correspond to each other, or NULL. In other words, I need to leave lines only where there are differences other than NULL. (If it matters, I will run it several times against tables with a different number of compared columns, sometimes a lot of columns, so all queries that focus on only three columns will not work.)

So if my table is:

ID  TITLE   VALUE_1 VALUE_2 VALUE_3
1   One     AAA     NULL    NULL
2   Two     NULL    AAA     AAA
3   Three   AAA     AAA     AAA
4   Four    NULL    NULL    NULL
5   Five    AAA     BBB     CCC
6   Six     AAA     BBB     NULL
7   Seven   NULL    DDD     EEE
8   Eight   AAA     AAA     BBB

After completing the request, I want to have

ID  TITLE   VALUE_1 VALUE_2 VALUE_3
5   Five    AAA     BBB     CCC
6   Six     AAA     BBB     NULL
7   Seven   NULL    DDD     EEE
8   Eight   AAA     AAA     BBB

Thanks again for your time.

Answer 1

select * from table
where v1 <> v2 or v1 <> v3 or v2 <> v3

, TRUE . , NOT IN :

delete t where id not in(select id from t where v1 <> v2 or v1 <> v3 or v2 <> v3)

http://sqlfiddle.com/#!3/ea7172/8

Answer 2

 Delete from tablename 
      where (Value_1 is Null And value_2 is null) 
             or (Value_2 is Null And value_3 is null)
             or (Value_1 is Null And value_3 is null)
             or (Value_1 = value_2)
             or (Value_1 = value_3)
             or (Value_3 = value_2)

, . sql- ,

Answer 3

You can use the ISNULLlike in the instructionsDELETE

Where IsNull (Value_1, 'SomeValueThatWillNeverBeInTheTable') = IsNull (Value_2, 'SomeValueThatWillNeverBeInTheTable')