Need to consolidate UPDATE statements


Each of these update statements works as they are.  I want to consolidate them into one statement--every time I try that, I don't get the results I'm expecting.

Basically I have a working table where I have a store number that can be either a specific store number, a district or a division. All is well when the granularity is at the store level--no updates needed.

On the division level I need only the division attribute, on the district level, I need the division level and the district attributes from the view.

Please advise.
-- //  division found in storenumber column // --
 UPDATE temp
  SET temp.Division = d.Division
 FROM vw_pld_bm_stores d
  INNER JOIN  ditallo_tblTempMonthCT_LS temp on d.Division = temp.StoreNumber
  WHERE d.Division IN
    (select distinct Division from vw_pld_bm_stores)   

-- // district found in storenumber column // --

  SET temp.District = d.District,
      temp.Division = d.Division
 FROM vw_pld_bm_stores d
  INNER JOIN  ditallo_tblTempMonthCT_LS temp on d.District = temp.StoreNumber
  WHERE d.District IN
    (select distinct district from vw_pld_bm_stores)

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?

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

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.

You can do it with case statement but why do you wan't to consolidate them. Right now they are easy to use and once they are combined, you have to logically check each time in the set statement and do any extra join which will degrade the performance.

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
As per my understanding, please find Consolidated Update statement

UPDATE T SET T.Division = Case when d.Division is null then d2.Division else d.Division end, T.District = d2.District,
FROM ditallo_tblTempMonthCT_LS T
  LEFT JOIN vw_pld_bm_stores d on d.Division = T.StoreNumber AND d.Division IN (select distinct Division from vw_pld_bm_stores)
  LEFT JOIN vw_pld_bm_stores d2 on d2.District = T.StoreNumber AND d2.District IN (select distinct district from vw_pld_bm_stores)
  WHERE d.Division is not null or d2.Division is not null

Let me know if that helps
David ToddSenior Database AdministratorCommented:

I agree with TempDBA. You haven't said what you hope to gain from the consolidation. The consolidation is likely to end up with a way more complex query, and potentially far worse performance.

I suggest that there maybe a modelling/design issue if temp.StoreNumber can map to both division and district.

Paula DiTalloIntegration developerAuthor Commented:
kinjolin's solution works perfectly, but in the end, the separate update statements are clearer to read for future maintenance.
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.