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

Question

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!

Does this answer your question? [I want to use CASE statement to update some records in sql server 2005](https://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005) — Bryan Dellinger, May 12 '22 at 17:52
@BryanDellinger thanks but no, that's not much nicer than copy pasting UPDATE statement 32 times for my records. — UnskilledCoder, May 12 '22 at 17:54

score 3 · Accepted Answer · answered May 12 '22 at 20:10

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.

score 1 · Answer 2 · answered May 12 '22 at 18:18

1

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

answered May 12 '22 at 18:18

Bryan Dellinger

4,724
7
33
79

Oh nice idea about CTE. I was writing it right now with a temp table and inner join like yours but CTE is fancier. I will use that. Thanks. – UnskilledCoder May 12 '22 at 18:20

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

2 Answers2