CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Steam
Steam

Reputation: 9866

Update using case statement

Given a table:

ColumnPosition, ColumnName
   Col 1,          NULL
   Col 2,          NULL
   Col 3,          NULL

I want to update ColumnName according to a predefined value mapping based on ColumnPosition.

For example:
If ColumnPosition = 'Col 1' then update ColumnName to be 'Name' If ColumnPosition = 'Col 3' then update ColumnName to be 'Address'

How can this be done using CASE statement?

Upvotes: 1

Views: 322

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

Instead of doing a bunch of case statements you could set up a map and then do a join

With Map as 
(
    SELECT       'Col 1' ColumnPosition , 'Name' as ColumnName
    UNION SELECT 'Col 3' , 'Address'
 )
UPDATE Table1 
SET Table1.ColumnName = Map.ColumnName
FROM
  table1  
  INNER JOIN MAP 
  ON Table1.ColumnPosition = Map.ColumnPosition;

DEMO

Upvotes: 5

Yosi Dahari
Yosi Dahari

Reputation: 7009

I think you mean UPDATE and not INSERT:

UPDATE MyTable
SET ColumnName = CASE WHEN ColumnPosition = 'Col 1' THEN 'Name'
                      WHEN ColumnPosition = 'Col 3' THEN 'Address'
                      -- ... the rest of your conditions
                      ELSE -- put your default value here
                 END

Upvotes: 6

Related Questions