Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Complex Update with SubQuery

Would appreciate help writing this Update Statement.
The big picture is that I have a report table that contains product IDs and other attributes about the product,  I need to go to a complex join of tables to pull three values and update each of those 3 columns in the report table.

The table Reports contains information a product
    ProductID, minPrice, maxPrice, AvgPrice, quantity on hand, etc.

This is the way I would normally do the update...
Update Reports 
   set R.minPrice = sub.minPrice
       , R.maxPrice = sub.maxPrice
       , R.avgPrice   = sub.avePrice
from Reports r
inner join (
  select product_id
           , min(price) as minPrice
           , max(price) as maxPrice
           , avg(price) as avgPrice
   from products
   group by product_id
 )  sub on sub.product_id = reports.product_id
where R.report ID = 123

Open in new window



However, in this case, I have a ton of variables for each product that effect the calculation on min/max/avg.
I really feel that I cannot possibly put all those variations on the select line to include in the join.
There are a lot of combinations of values that I eed to use in the sub query, some involve all 50 states combined with color options, price ranges and everything else.   Here is my attempt at a mock-up to give you the idea:::

Update Reports 
   set R.minPrice = sub.minPrice
       , R.maxPrice = sub.maxPrice
       , R.avgPrice   = sub.avePrice
from Reports r
inner join (
  select product_id
           , state
           ,colorArray
           ,colorOption
           ,orderOption
           ,levelRange
           ,statDepth
           ,innerFlank
           , min(price) as minPrice
           , max(price) as maxPrice
           , avg(price) as avgPrice
   from products
    inner  join ...
    left  join ...
    where p.innerFlank = case when r.reportOption = 1 or r.reportOption is null then 1 else 7 end
     and  exists (select 1 from productCases
              where caseThickness = r.ReportProductWidth
              )
   group by product_id, and long list...
 )  sub on sub.product_id = reports.product_id
where R.report ID = 123

Open in new window


So, in summary, I need to pull values from a sub query based on several values in the report table and return 3 values to update the report table.

If I didn't have to return three values, I would simply use a function
     SET maxPrice = dbi.myComplexFunction(prodcutID, state, colorOption, flankPatern, blah, blah)

But I have to return 3 values, which would mean three functions.  

I understand that Oracle has a way to do this...

   update reports
       set maxPrice, minPrice, avgPrice = (select maxPrice, minPrice, avgPrice
             from ..... long complex joins and conditions
             )
    where report_id = 123

I could do that if possible in SQL server because I could use the report columns inside the SubQuery !

How do I fetch 3 columns for updates from a subquery that can use all the columns values from the outter tables..

Thanks!

SQL Server 2012
0
gdemaria
Asked:
gdemaria
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
You can use CROSS APPLY instead of INNER JOIN.

Note, too, that when UPDATEing using a join/embedded query, be certain to update the alias name rather than original table name, otherwise you can get incorrect updates:

Update r /*not "Reports"*/
   set R.minPrice = sub.minPrice
       , R.maxPrice = sub.maxPrice
       , R.avgPrice   = sub.avePrice
from Reports r
cross apply (
  select  min(price) as minPrice
           , max(price) as maxPrice
           , avg(price) as avgPrice
   from products
    inner  join ...
    left  join ...
    where p.innerFlank = case when r.reportOption = 1 or r.reportOption is null then 1 else 7 end
     and  exists (select 1 from productCases
              where caseThickness = r.ReportProductWidth
              )
   group by product_id, and long list...
 )  sub on sub.product_id = reports.product_id
where R.report ID = 123
0
 
gdemariaAuthor Commented:
Hi Scott,
Thanks for your reply.

It is imperative that I am able to use variables from out reports table within the join.   You can do this with a subquery in the select, but you cannot do this with a subquery with a join.

Are you able to do it with a cross apply?

For example, you see this clause in the subquery where REPORTS.Product_ID is part of the table outside of the sub query

     and  exists (select 1 from productCases
              where caseThickness = r.ReportProductWidth

Will that work?

Thanks !!
0
 
gdemariaAuthor Commented:
A quick search, I was able to answer my own question.  That is exactly what the cross apply does.

Thanks very much!
0
 
gdemariaAuthor Commented:
Great, fast answer... thank you
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now