Menu
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?
408
The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.
Below is the syntax to filter the rows without a null value in a specified column. Syntax: SELECT * FROM <table_name> WHERE <column_name> IS NOT NULL; Example: SELECT * FROM demo_orders WHERE ORDER_DATE IS NOT NULL; --Will output the rows consisting of non null order_date values.
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.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
