CODEWITHSUNDEEP
databasesqlite
Fostah
Fostah

Reputation: 2946

SQLite Update columns only if value is not empty

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

Question: How can I update field1 with field1_spanish ONLY if field1_spanish is not empty ? I would like to update field2 with field2_spanish also if field2_spanish is not empty.

Thanks!

Upvotes: 7

Views: 8909

Answers (2)

Jake Feasel
Jake Feasel

Reputation: 16955

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

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

The coalesce() function will return the first of the arguments passed to it which 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

Upvotes: 18

user554546
user554546

Reputation:

Assuming that 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 and field2 are in table and the *_spanish columns are in table_spanish, then...well, SQLite doesn't support a from clause in an update statement, so you'll have to do a correlated subquery. Assuming that table has a primary key of id that is referenced by 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 a staging table via a join and then delete the relevant entries from table and insert the new data from the staging table (make sure to use a transaction for all of that!).

Hat tip to martin clayton for the second approach.

Upvotes: 1

Related Questions