Sql query for updating database if value is not null?

Question

I am having a table which has about 17 fields. I need to perform frequent updates in this table. But the issue is each time I may be updating only a few fields. Whats the best way to write a query for updating in such a scenario? I am looking for an option in which the value gets updated only if it is not null.

For example I have four fields in database Say A,B,C,D. User updates the value of say D. All other values remains the same. So I want an update query which updates only the value of D keeping the others unaltered. SO if i put a,b and c as null and d with the value supplied by user I want to write an update query which only updates the value of d as a,b and c is null. Is it something achievable?

I am using SQLite database.

Could someone please throw some light into it?

What database do you Use? MySQL? Oracle ? SQL Server ? You can do such a thing using triggers (Before update). If your database supports it. — Marc Bouvier, May 23 '11 at 15:47
I am using sqlite ..... I am looking for an sql command for updating... — Zach, May 23 '11 at 15:48

score 62 · Accepted Answer · answered May 23 '11 at 15:50

62

Without knowing your database it's tough to be specific. In SQL Server the syntax would be something like ...

UPDATE MyTable 
SET 
        Field1 = IsNull(@Field1, Field1),
        Field2 = IsNull(@Field2, Field2),
        Field3 = IsNull(@Field3, Field3)
WHERE 
     <your criteria here>

EDIT

Since you specified SQLLite ...replace my IsNull function with COALESCE() or alternately look at the IfNull function.

answered May 23 '11 at 15:50

EBarr

11,826
7
63
85

12

Just keep in mind that this means that you can never set a column back to NULL using this stored procedure once it's been set. – Tom H May 23 '11 at 16:13
In MySQL the @ is not needed, i.e. `Field1 = IsNull(Field1Value)` seems to work fine – Janac Meena Apr 09 '20 at 15:26
@Janac Meena: The name of the function to use in Mysql for this cases is `IfNull` – David Lopez May 07 '20 at 17:38

score 2 · Answer 2 · answered Nov 18 '15 at 22:18

Posting a SQL Server solution with 2 tables for posterity. Query joins two tables and updates the values that are present. Otherwise original value is maintained.

tables = table1, table2 each having field1 and field2

update t1 WITH (ROWLOCK)
set T1.Field2 = ISNULL(T2.Field2,T1.Field2)
from Table1 T1 Join Table2 T2 
    ON T1.Field1 = T2.Field1

Sql query for updating database if value is not null?

2 Answers2

Linked