• Welcome, Guest. Please login.
 
August 20, 2019, 08:52:40 pm

News:

Welcome to the SQLitening support forums!


SQL Update Question

Started by D. Wilson, December 15, 2017, 05:12:26 pm

Previous topic - Next topic

D. Wilson

I know sqlite is typeless in column types. I am stumped. I would like to update the value of a field however. If the field is null it will not update the field. For Example:
                               Update INVENTORY set qty_on_hand = qty_on_hand + 5 Where Itemcode ='Item Code'

                         If the original value is 10. The server would update it to 15.
                         If the original value is null (no value in the field). The server does not apply the change.


Any help and insights would be greatly appreciated.


D. Wilson

I did a work around and added and additional sqlstatement checking for isnull. But if there is an easier way I would appreciate any insights.

Bern Ertl

December 15, 2017, 06:47:03 pm #2 Last Edit: December 15, 2017, 06:49:00 pm by Bern Ertl
To update all cases:

Update INVENTORY set qty_on_hand = ifnull(qty_on_hand,0) + 5 Where Itemcode ='Item Code'

http://www.sqlite.org/lang_corefunc.html#ifnull

To filter out NULL cases:

Update INVENTORY set qty_on_hand = qty_on_hand + 5 Where Itemcode ='Item Code' AND qty_on_hand IS NOT NULL

cj

December 15, 2017, 10:16:40 pm #3 Last Edit: December 16, 2017, 12:01:48 pm by cj
Insight: Get rid of NULL because they are not needed
slexe  "update t1 set Onhand = 0  where OnHand is null"

Bern has given complete answer

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
slopen "sample.db3"
slexe  "drop table if exists t1"
slexe  "create table if not exists t1(Code unique,OnHand integer)"
slexe  "insert into t1 values('code1',0)"
slexe  "insert into t1 values('code2',0)"
slexe  "insert into t1 values('code3',null)"
slexe  "update t1 set OnHand = OnHand + 1 where code = 'code1'"
slexe  "update t1 set Onhand = OnHand + 2 where code = 'code2'"
slexe  "update t1 set Onhand = ifnull(Onhand,0) + 3 where code = 'code3'"
DIM s() AS STRING
slselary "select * from t1",s(),"Q9"
? JOIN$(s(),$CR),%MB_SYSTEMMODAL
END FUNCTION         
       

D. Wilson

Add the ifnull to sqlstatement worked like a charm.

I want to thank those who jumped in and helped solve this. I spent hours trying to 'find' then answer on my own.