SQLTeam.com | Weblogs | Forums

Update Statement Assistance


#1

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...

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

etc..


#2
select code + '-' + code_name from yourtable

#3

I tried that and got an error ...


#4

Try

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

That is if Code is character.


#5

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)


#6

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