SQLite Update columns only if value is not empty
update item_table set field1 = coalesce(field1_spanish, field1), field2 = coalesce(field2_spanish, field2)
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
source to share
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;
, and columns
, then ... well, SQLite doesn't support clause
, so you'll have to execute a correlated subquery. Assuming it
referenced primary key
, 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
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.