7

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!

Fostah
  • 2,947
  • 4
  • 56
  • 78

2 Answers2

18

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
Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • 7
    BTW - sqlfiddle.com is my site – Jake Feasel Mar 21 '12 at 23:04
  • Awesome site and answer! Thanks! – Fostah Mar 21 '12 at 23:07
  • @JakeFeasel - Excellent site! I do wish it wouldn't hijack my back button, however... –  Mar 21 '12 at 23:17
  • @JackManey It doesn't really hijack your back button, but instead only adds more states to your browsing history as you run queries. Hit "back" enough and you should eventually leave the site, after you've stepped through your previous work. – Jake Feasel Mar 21 '12 at 23:20
  • @JakeFeasel I am running into one small problem, for some reason it is treating No value in the column aka "" as NOT NULL. I tried changing the schema to name varchar(255) null, name_lang1 varchar(255) null but it seems its still returning not null. Any ideas? – Fostah Mar 21 '12 at 23:52
  • 3
    Well, empty string is not the same as null. You'll instead have to do something like this: http://sqlfiddle.com/#!5/b344f/3 – Jake Feasel Mar 22 '12 at 00:06
  • @JakeFeasel That was exactly what I needed! Sorry about the confusion. I updated the question with empty/instead of null – Fostah Mar 22 '12 at 00:35
1

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.

Community
  • 1
  • 1