Solved

Complex Update with SubQuery

Posted on 2016-10-26
4
32 Views
Last Modified: 2016-10-26
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
Comment
Question by:gdemaria
  • 3
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41860725
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
 
LVL 39

Author Comment

by:gdemaria
ID: 41860806
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
 
LVL 39

Author Comment

by:gdemaria
ID: 41860823
A quick search, I was able to answer my own question.  That is exactly what the cross apply does.

Thanks very much!
0
 
LVL 39

Author Closing Comment

by:gdemaria
ID: 41860825
Great, fast answer... thank you
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now