Updating one column based on the value of another column #2

Have a table (settings) with 1000+ rows, and two columns (name, value). I need to update a handful of values, based on the name.

Using Updating one column based on the value of another column I get "Invalid Column Name 'AppName' and AppID'".

update Test.dbo.Setting
set Value = (CASE 
               WHEN Name = AppName
                 THEN 'Test1'
               WHEN Name = AppID
                 THEN 'Test2'
               END);

Why would this work in the other thread, but not in my example? In any case I moved on to wrap the AppName and AppID in single quotes.

update Test.dbo.Setting
set Value = (CASE 
               WHEN Name = 'AppName'
                 THEN 'Test1'
               WHEN Name = 'AppID'
                 THEN 'Test2'
               END);

Which comes back with "Cannot insert the value NULL into column 'Value', table 'Test.dbo.Config'; column does not allow nulls. UPDATE fails." Which is true, it doesn't allow NULLs, and my thought is that because there is no ELSE statement it's trying to write NULL to the rest of the 1k+ values.

So lets try it with else..

update Test.dbo.Setting
set Value = (CASE 
               WHEN Name = 'AppName'
                 THEN 'Test1'
               WHEN Name = 'AppID'
                 THEN 'Test2'
               ELSE Value
               END);

Oops, it works, but also overwrites all Values to what they currently are.. so while it "works" I do not want to touch the other values other than the ones I specify..

Aside from a complete mess of what to try next.. any better ideas on how to optimize?

Thanks.

2 answers

  • answered 2018-01-11 20:04 pmbAustin

    Just add a WHERE clause to update only the rows you want to update:

    UPDATE Test.dbo.Setting
       SET [Value] = (CASE WHEN [Name] = 'AppName'
                           THEN 'Test1'
                      WHEN [Name] = 'AppID'
                           THEN 'Test2'
                      END)
     WHERE [Name] IN ('AppName', 'AppID')
    

  • answered 2018-01-11 20:06 ViKiNG

    You can always try following to only update the rows you want without overwriting value values:

    update Test.dbo.Setting
    set Value = (CASE 
                   WHEN Name = 'AppName'
                     THEN 'Test1'
                   WHEN Name = 'AppID'
                     THEN 'Test2'
                   END)
    WHERE Name IN ('AppName','AppID')