SQLite Update columns only if value is not empty

Request: UPDATE item_table SET field1=field1_spanish, field2=field2_spanish;

Question: How to update field1

using field1_spanish

ONLY if field1_spanish

not empty? I would like to update field2

with help field2_spanish

if field2_spanish

not empty.

Thank!

+3


source to share


2 answers


http://sqlfiddle.com/#!5/58554/1

update
  item_table
set
  field1 = coalesce(field1_spanish, field1),
  field2 = coalesce(field2_spanish, field2)

      

The function coalesce()

will return the first argument passed to it that is not null. So in this case, since field2_spanish is NULL, it will set field2 to field2 (basically doing nothing).



And to support empty strings and NULL values ​​try this: http://sqlfiddle.com/#!5/b344f/3

update
  item_table
set
  field1 = case when coalesce(field1_spanish, '') = '' then
            field1
           else
            field1_spanish
           end,
  field2 =  case when coalesce(field2_spanish, '') = '' then
            field2
           else
            field2_spanish
           end

      

+10


source


Assuming all of these columns are in the same table:

update some_table
set field1=field1_spanish,
field2=field2_spanish
where field1_spanish is not null
and field2_spanish is not null;

      

If field1

u field2

are in table

, and columns *_spanish

are in table_spanish

, then ... well, SQLite doesn't support clause from

in statementupdate

, so you'll have to execute a correlated subquery. Assuming it table

has a id

referenced primary key table_spanish

, you can do:



update table a
set field1=(select s.field1_spanish 
    from table_spanish s 
    where field1_spanish is not null
    and s.id=a.id),
field2=(select s.field2_spanish 
    from table_spanish s 
    where field2_spanish is not null
    and s.id=a.id);

      

Or you can populate the staging table via a join and then delete the corresponding records from table

and insert new data from the staging table (be sure to use a transaction for all of this!).

hat tip to martin clayton for second approach.

+1


source







All Articles