Solved

Complex Update with SubQuery

Posted on 2016-10-26
4
39 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 97
CPU high usage when update statistics 2 30
Passing Parameter to Stored Procedure 4 24
optimize stored procedure 6 29
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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

832 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