CODEWITHSUNDEEP
sqlsql-serversql-server-2012
CGCIC
CGCIC

Reputation: 3

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.

Upvotes: 0

Views: 110

Answers (2)

ViKiNG
ViKiNG

Reputation: 1334

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')

Upvotes: 0

pmbAustin
pmbAustin

Reputation: 3980

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')

Upvotes: 1

Related Questions