Inserting new values in a table if SQL had Dictionary and for-each-CodePudding

Let's say my table of myTable has a column1 that has some values already in it. Now I am given some new values that I should put in a newly created column named 'column2' . These are one to one associated together and unique. So for example:

column1 | column2
-----------------
'ABCHi' | 'newOH'
-----------------
'TER12' | 'Meow2'
-----------------
'WhatE' | 'BMW26'
-----------------

So I could say like:

Update myTable SET column2 = 'newOH' WHERE column1 = 'ABCHi'

and do that for each of those rows ( I have 32 of them to do ). But I thought maybe there is a "nicer" way of doing this? Like if it was C# I could say populate a dictionary and then do a for-each loop!

CodePudding user response：

You can use a Table Value Constructor:

declare @Samples as Table ( Column1 VarChar(10), Column2 VarChar(10) );

-- Initialize the sample data.
insert into @Samples ( Column1 ) values
  ( 'ABCHi' ), ( 'TER12' ), ( 'WhatE' )
select * from @Samples;

-- Update the second column.
update OSamples
  set Column2 = NSamples.Column2
  from @Samples as OSamples inner join
    ( values
      ( ( 'ABCHi' ), ( 'newOH' ) ),
      ( ( 'TER12' ), ( 'Meow2' ) ),
      ( ( 'WhatE' ), ( 'BMW26' ) )
      ) as NSamples( Column1, Column2 )
    on OSamples.Column1 = NSamples.Column1;
select * from @Samples;

DBfiddle.

CodePudding user response：

you could create the "Dictionary" as an inline view using the With clause.

here is the fiddle https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c0e1393785082fd5cd9352d513b76af6

with Dictionary as(
  select 'ABCHi' as column1, 'newOH' as column2 
  union all
  select 'TER12' as column1, 'Meow2' as column2 
  union all
  select 'WhatE' as column1, 'BMW26' as column2
  )
UPDATE t
SET  t.column2=dictionary.column2
FROM mytable t JOIN Dictionary ON t.column1 = Dictionary.column1