Update and append unless empty

问题:

Im trying to update a field by appending data to it. if it contains the data already i wont update it otherwise I will.

if it already contains data i want it to append a comma and space followed by the word. e.g.

update myTable 
set  Prefixes = convert(nvarchar(max),Prefixes) + ', abc' 
where MyCol='xyz' and Prefixes not like '%abc%'

im trying to get this to work so that if the prefixes column is empty initially it only includes the word 'abc'

and not ', abc'

How can i do this?

回答1:

Sounds like you need a CASE:

update myTable 
set  Prefixes = 
           case 
               when Prefixes is null or Prefixes = ''
               then 'abc'
               else convert(nvarchar(max),Prefixes) + ', abc' 
            end
where MyCol='xyz' and (Prefixes not like '%abc%' or Prefixes is null)

See SQL Fiddle with Demo

回答2:

You need to check for null values of Prefixes before filtering as NOT LIKE in the WHERE clause as well. Sql-Demo

update myTable 
set  Prefixes = isnull(nullif(rtrim(Prefixes),'') + ', abc','abc')
where MyCol='xyz' and isnull(Prefixes,'') not like ', abc%'