SQL replace or update for multiple values in a column

Hi,

I am having a challenge where I need to update multiple values in a column of a SQL database where I need to add a code to the existing value.

Example:

Now:      
Apples
Oranges
Bananas
Grapes

I need to change it to:
Apples - 1234
Oranges - 2345
Bananas - 3456
Grapes - 4567

Any help is appreciated.

Thanks,

Whah
WhahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
How do you determine the code? Do you have the code in any other column?
0
WhahAuthor Commented:
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
0
SharathData EngineerCommented:
insert the data from the xls into a temp table and then update the data in actual table.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
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

Open in new window

0
WhahAuthor Commented:
Thank you very much for the help. I will give this a try today, and let you know how it goes. Thanks again.
0
WhahAuthor Commented:
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.
0
WhahAuthor Commented:
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
0
SharathData EngineerCommented:
try like this.
UPDATE A
   SET A.Column2 = CONVERT(NVARCHAR(10),B.Column2)+'-'+A.Column1
  FROM TableA A
  JOIN TableB B
    ON A.Column1 = B.Column1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WhahAuthor Commented:
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.
0
WhahAuthor Commented:
Thanks, Sharah. I really appreciate your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.