Solved

Complex Update with SubQuery

Posted on 2016-10-26
4
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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