lazarov lazarov - 4 months ago 7
SQL Question

Insert NULL if value matches the value of another column

Lets say I have a table : "MyTable" and I have two columns in it : "val" and "val_new".

Now I want to insert new value into "val_new" but if the values are equal('val' and 'val_new') I want to insert NULL instead.

----------------------
| id | val | val_new |
----------------------
| 1 | 5 | NULL |
----------------------
| 2 | 6 | NULL |
----------------------


Lets have this table for example.

Now :

UPDATE myTable mt
SET mt.val_new = '5'
WHERE mt.id = '1';


I want the value of val_new to remain NULL or be updated to NULL instead of '5'.

EDIT:
I want to UPDATE existing values not INSERTING new rows.

Answer

You need not have to insert a record rather you will have to update the existing one. If you run your insert command a new record will be created. So you table will have

----------------
|val | val_new |
----------------
| 5  |  NULL   |
----------------
| 6  |  NULL   |
----------------
|    |         | <-- if val = val_new
----------------
|    |   6     | <==if val<> val_new.

I guess you dont need this output. So the best option is to update the columns.

You can use case statement,

update <yourtable>
set val_new =case 
                when  val_new= val   then 
                NULL 
               else val_new
             end