仅当值不为空时，SQLite Update列 [英] SQLite Update columns only if value is not empty

问题描述

查询： 更新item_table SET field1 = field1_spanish，field2 = field2_spanish;

问题：如何使用 field1_spanish 仅当时更新 field1 strong> field1_spanish 不为空吗？我想用 field2_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.

谢谢！

推荐答案

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

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

coalesce（）函数将返回传递给它的第一个参数，该参数不为null。因此，在这种情况下，由于field2_spanish为null，因此会将field2设置为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).

并支持空字符串和NULL值，请尝试以下操作：
http://sqlfiddle.com/#!5/b344f/3

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

这篇关于仅当值不为空时，SQLite Update列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！