Conditional update - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional update

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-16 : 08:57:01

I have a table:


salutation forname surname correspondence
---------- ------- ------- --------------
Mr         Bill    Smith   Mr Bill Smith
Mrs        Sue     Smith   Mrs Sue Smith
Mr         Matt    Jones   Mr Jones

I need to update the correspondence field so that it becomes
salutation + ' ' + forename initial + ' ' + surname only if the existing
field does not equal 'salutation' + ' ' + 'forename' + ' ' + 'surname'.

So the resulting rows from the example table above would return


salutation forname surname correspondence
---------- ------- ------- --------------
Mr         Bill    Smith   Mr B Smith
Mrs        Sue     Smith   Mrs S Smith
Mr         Matt    Jones   Mr Jones

(row 3 has not been updated as it deviates from the expected norm).

How can I achieve this please?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 09:03:57

Maybe:

Update table
Set correspondence = (Case when correspondence = salutation
+' ' +forname+ ' ' + surname 
then Salutation +' '+ 
Left(forname,1)+' ' + surname else 
correspondence end)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-16 : 09:07:28

[code]Update yourtable
set correspondence=salutation+' '+left(forname,1)+' ' +surname
where
correspondence=salutation+' '+forname+' ' +surname[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 09:12:17

[code]update table
set correspondence=stuff(correspondence,charindex('% '+forname+ ' %',correspondence)+3,len(forname)-1,'')
where correspondence = coalesce(salutation+ ' ','')+ coalesce(forname+ ' ','')+coalesce(surname,'')
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 09:13:15

quote:

Originally posted by sakets_2000

Update yourtable 
set correspondence=salutation+' '+left(forname,1)+' ' +surname 
where
correspondence=salutation+' '+forname+' ' +surname

what if salutation, forname & surname are nullable

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-16 : 09:26:53

quote:
Originally posted by visakh16

quote:
Originally posted by sakets_2000

Update yourtable
set correspondence=salutation+' '+left(forname,1)+' ' +surname
where
correspondence=salutation+' '+forname+' ' +surname

what if salutation, forname & surname are nullable

Luckily they are not. Otherwise I would have to say to you "Don't be awkward"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-16 : 09:27:36

Many thanks for all the replies guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources