SQL Conditional UPDATE statement

Hi Experts

I have a table where there are these fields:-

POBox
SubPremisesNameNumber
PremisesName
PremisesNumber
Thoroughfare
DependentThoroughfare
DoubleDependentLocality
DependentLocality

I need to update in the same table Addr1, Addr2 and Addr3 fields with the above fields data.

An example would be:-
Addr1 would be updated with POBox but if POBox IS NULL I want to update Addr1 with SubPremisesNameNumber instead. And so on..

There will be times where I need to concatenate say 'PremisesNumber' + 'Thoroughfare' and others depending on how many source fields are populated.

Hope you can help me?

Regards, naexpert
naexpertAsked:
Who is Participating?
 
gothamiteConnect With a Mentor Commented:
You can do
UPDATE table SET Addr1=ISNULL(POBox,'')+ISNULL(SubPremisesNameNumber,'')
0
 
gothamiteCommented:
You probably need to use a CASE statement e.g.

UPDATE table SET Addr1=CASE WHEN POBox IS NOT NULL THEN POBox ELSE SubPremisesNameNumber END
0
 
Patrick MatthewsCommented:
UPDATE SomeTable
SET Addr1 = COALESCE(POBox, SubPremisesNameNumber)

takes care of addr1.  For the other columns, please indicate what needs to happen.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
naexpertAuthor Commented:
>>UPDATE SomeTable
>>SET Addr1 = COALESCE(POBox, SubPremisesNameNumber)

I tried COALESCE but where there is data in both POBox and SubPremisesNameNumber it only updates with POBox and leaves out SubPremisesNameNumber

I was hoping this solution might work but alas I don't think it will.

Cheers, naexpert.
0
 
naexpertAuthor Commented:
>>UPDATE table SET Addr1=CASE WHEN POBox IS NOT NULL THEN POBox ELSE SubPremisesNameNumber END

I think this one might work better but how would I deal with the scenario where there is data in both source fields, I think that's going to get complicated. I'm wondering whether it might be better to use CONCATENATE and be done with it!

What do you think?
0
 
naexpertAuthor Commented:
This did it for me!

UPDATE [Test].[dbo].[AddrTest]
SET Addr1=LTRIM(ISNULL(POBox,'')+' '+ISNULL(SubPremisesNameNumber,''))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.