update statement with concatenation

Question

update statement with concatenation

_taz_

Ten Centuries

Points: 1225
More actions
June 1, 2010 at 4:38 pm

#233309

is update statement with concatenation possible? (maybe not the right term)
I have inherited DB where I think it was done before, but not sure
have 3 columns, lastname,companyid,ssn and 4th column CID
CID is company hyphen first3lastname,last4ssn
so
lastname smith
companyid 80
ssn 123-45-1234
would be
80-smi1234

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Yes, you can update your CID field if you want to. However, generally it's better to leave the data split into the first 3 columns and then do the concatenation either on the client side or as part of the proc that returns the data set that you need to work with.

If you need to store it as a separate field for a specific reason you could also look at using a computed column. That way you wouldn't have as much to worry about if one of the three columns gets updated.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

_taz_ Ten Centuries Points: 1225 More actions · Answer 2

yes I need to store it seperate for now.

I have never done an update with a calculation. just simple

update table

set columnx=y

where columnz = null

type of stuff

thanks

WayneS SSC Guru Points: 95461 More actions · Answer 3

_taz_ (6/1/2010)
yes I need to store it seperate for now.
I have never done an update with a calculation. just simple
update table
set columnx=y
where columnz = null
type of stuff
thanks

First, I definitely agree with making this a separate, CALCULATED column instead of a separate, PERSISTED column. The calculated column will be unaffected with one of your three underlying columns changes... it will always display the correct data. By having a separate persisted column, whenever you update one of those three, you also need to update this column.

Either way, the calculation is the same:

convert(varchar(10), company) + '-' + left(lastname,3) + right(ssn,4)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 4

WayneS (6/1/2010)
_taz_ (6/1/2010)
yes I need to store it seperate for now.
I have never done an update with a calculation. just simple
update table
set columnx=y
where columnz = null
type of stuff
thanks
First, I definitely agree with making this a separate, CALCULATED column instead of a separate, PERSISTED column. The calculated column will be unaffected with one of your three underlying columns changes... it will always display the correct data. By having a separate persisted column, whenever you update one of those three, you also need to update this column.
Either way, the calculation is the same:
convert(varchar(10), company) + '-' + left(lastname,3) + right(ssn,4)

Ugh... be carefull. "CID" is an "ID" and if someone gets married and changes their last name, a calculated column will also change possibly blowing references to the CID column out of the water.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

_taz_ Ten Centuries Points: 1225 More actions · Answer 5

Wayne, thanks that's what I needed

Jeff, I really hadn't thought about that but you are right. but once I get this column populated correctly, I can merge it with my DB then, have it go off autonumber for CID