Please help an amateur - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help an amateur

Author

Topic

tchelcho
Starting Member

4 Posts

Posted - 2009-04-07 : 13:11:00

I need to UPDATE a column in a table to include a prefix.
The prefix I need in front of each code is "LAB-"

TABLE NAME = Service

CODE | DESC
10010 Complete Blood Count
10020 Troponin
10030 BMP
10031 CMP

I need it to look like:
CODE | DESC
LAB-10010 Complete Blood Count
LAB-10020 Troponin
LAB-10030 BMP
LAB-10031 CMP

How would I write a statement that would take care of these. There are almost two thousand rows so I would hate doing it manually.

Thanks for your assistance.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 13:12:54

You won't have to do it manually. Just look in BOL for UPDATE statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 13:14:07

just use

UPDATE Service SET CODE='LAB-'+CODE

also CODE should be of nonnumeric type for above

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-04-08 : 05:27:07

Don't forget to use CONVERT , as you will be ending up with a string

Jack Vamvas
--------------------
http://www.ITjobfeed.com

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-08 : 05:43:53

quote:
Originally posted by visakh16

just use

UPDATE Service SET CODE='LAB-'+CODE

also CODE should be of nonnumeric type for above

but if there happens a null in code, will not be updated
so i think we have to add

UPDATE Service SET CODE='LAB-' where CODE is null

ok tanx...

matty
Posting Yak Master

161 Posts

Posted - 2009-04-08 : 07:43:44

That is not correct.It will update only records with codes as null.

quote:
Originally posted by soorajtnpki

quote:
Originally posted by visakh16

just use

UPDATE Service SET CODE='LAB-'+CODE

also CODE should be of nonnumeric type for above

but if there happens a null in code, will not be updated
so i think we have to add

UPDATE Service SET CODE='LAB-' where CODE is null
ok tanx...

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-08 : 08:13:39

hi matty,
I said u have to add/execute UPDATE Service SET CODE='LAB-' where CODE is null
after executing query UPDATE Service SET CODE='LAB-'+CODE
then whole will be updated as required.

ok tanx...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-10 : 06:32:57

quote:
Originally posted by soorajtnpki

quote:
Originally posted by visakh16

just use

UPDATE Service SET CODE='LAB-'+CODE

also CODE should be of nonnumeric type for above

but if there happens a null in code, will not be updated
so i think we have to add

UPDATE Service SET CODE='LAB-' where CODE is null

ok tanx...

if CODE IS NULL then shouldnt it remain as NULL itself after updation? even in that case my solution will work as long as CONCAT NULL YIELDS NULL setting is on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources