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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
gothamiteCommented:
You can do
UPDATE table SET Addr1=ISNULL(POBox,'')+ISNULL(SubPremisesNameNumber,'')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
naexpertAuthor Commented:
This did it for me!

UPDATE [Test].[dbo].[AddrTest]
SET Addr1=LTRIM(ISNULL(POBox,'')+' '+ISNULL(SubPremisesNameNumber,''))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.