Need to consolidate UPDATE statements

Techies--

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 // --

UPDATE temp
  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?
 
TempDBACommented:
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.
0
 
kinjalinCommented:
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
0
 
David ToddSenior DBACommented:
Hi,

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.

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