SQLite Update columns only if value is not empty
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
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;
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.