Solved

Complex Update with SubQuery

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

737 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