SQL replace or update for multiple values in a column

How do you determine the code? Do you have the code in any other column?

Hi Sharath_123,

No, the code isn't in any existing columns. I have been given an excel spread sheet with all of the old values and their corresponding new values that now have a code associated with each one.

Thanks,

Brian

insert the data from the xls into a temp table and then update the data in actual table.

does this help?

    CREATE TABLE NewCodes
    	([GivenString] varchar(7), [WantedCode] int)
    ;
    	
    INSERT INTO NewCodes
    	([GivenString], [WantedCode])
    VALUES
    	('Apples', 1234),
    	('Oranges', 2345),
    	('Bananas', 3456),
    	('Grapes', 4567)
    ;
    
    
    
    CREATE TABLE FromExcel
    	([GivenString] varchar(7), [WantedCode] int)
    ;
    	
    INSERT INTO FromExcel
    	([GivenString], [WantedCode])
    VALUES
    	('Apples', NULL),
    	('Oranges', NULL),
    	('Bananas', NULL),
    	('Grapes', NULL)
    ;
    
    update FromExcel
    set FromExcel.WantedCode = NewCodes.WantedCode
    from FromExcel
    inner join NewCodes ON FromExcel.GivenString = NewCodes.GivenString
    ;
    

**Query 1**:

    select
    *
    from FromExcel
    

**[Results][2]**:
    
    | GIVENSTRING | WANTEDCODE |
    |-------------|------------|
    |      Apples |       1234 |
    |     Oranges |       2345 |
    |     Bananas |       3456 |
    |      Grapes |       4567 |



  [1]: http://sqlfiddle.com/#!3/d733b/1

Select allOpen in new window

Thank you very much for the help. I will give this a try today, and let you know how it goes. Thanks again.

Thank you for the suggestions so far, but I've gotten some more information on what really needs to be done.

TableA

Column1
Apples
Oranges
Bananas
Grapes

I have taken the Excel file and imported into another SQL table. What I need now is if TableA's Column1 matches TableB's Column1 then take the data from TableB Column2, and put it in a new column called Column2 in TableA. They want to retain the data as it appears in TableA Column1, and translate the new value that is in this other temp table with the new values, and add a new column with what the new value should be.

I need this:

TableA

Column1   Column2
Apples      01-Apples
Oranges    02-Oranges
Bananas    03-Bananas
Grapes      04-Grapes

Thank you very much for your help.

I may have simplified this too much in my example because really there are many instances of the different fruit in column1, and all need to have the corresponding new value in the new column. Just wanted to add this in case it matters. Thanks

Hi Sharath,

This is mine based from your example.

UPDATE HACSB_Test
   SET HACSB_Test.PROPERTY = CONVERT(NVARCHAR(50),TEMP.PROPERTY)+'-'+HACSB_Test.PROGRAM
  FROM HACSB_Test
  JOIN TEMP
    ON HACSB_Test.PROGRAM = TEMP.PROPERTY

I am getting 0 rows affected.

Just to further clarify, I need: If HACSB_Test.Program=(Whatever the match is in Temp.Program) then copy corresponding value from the Temp.PROPERTY to HACSB_Test.Property.

Both Program and Property exist in the "Temp" table with Temp.Program being the match of the old value in HACSB_Test.Program, and Property being the new value that only exists right now in Temp.Property that needs to get copied to the corresponding HACSB_Test.Property. I have already created the Property field in the HACSB_Test table.

Hope this helps, and thank you again for the help.

Thanks, Sharah. I really appreciate your help.