SQLTeam.com | Weblogs | Forums

Update Statement Assistance


Below is a result set ...I need to update Code_Name with a prefix (Code) and a hypen. I've tried a variety of statements on my own with no luck.

I'd want the result to look like this...

32 -Home Health-Inpatient
33 -Home Health-Outpatient
34 -Home Health-Other


select code + '-' + code_name from yourtable


I tried that and got an error ...



UPDATE Code_Sets
SET Code_Name = Code + ' - ' + Code_Name;

That is if Code is character.


For the record, this is a terrible way to store data. Each column should be a single piece of data. If you need to concatenate data, use a computed column to generate that for display purposes.

At any rate, this should do it:

UPDATE Code_Sets
SET Code_Name = CAST(Code AS varchar(10)) + ' -' + Code_Name
WHERE Code_Name NOT LIKE '[0-9]%' --only if number not already prefixed to value (in case a re-run is required)


Ahhhhh!!! Yes! light bulb... you're right. I set up a computed column and that worked well. Thank you.