SQL syntax needed to convert select with join in to an update statement

I have recently discovered that on a few quotes the total of the sell price on the quote header does  not equal the total of the sell prices from the lines. Is it possible to turn this select statement into an update statement where CSTQUTHD.TOT_SELL_PRC = SUM(CSTQUTLN.TOT_SELL_PRC)?

SELECT T1.QUOTE_NUMBER,T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB,T1.TOTAL_SELL_PRC,T2.TOTAL_SELL_PRC
FROM (
SELECT QUOTE_NUMBER_OEQH AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQH AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQH AS REVALIDATE_NUMB,TOTAL_SELL_PRC_OEQH AS TOTAL_SELL_PRC
FROM CSTQUTHD ) T1
INNER JOIN (
SELECT QUOTE_NUMBER_OEQL AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQL AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQL AS REVALIDATE_NUMB,SUM(QUOTE_QUANTITY_OEQL*QUOTE_SELL_PRIC_OEQL) AS TOTAL_SELL_PRC
FROM CSTQUTLN
GROUP BY QUOTE_NUMBER_OEQL,SEQUENCE_NUMBER_OEQL,REVALIDATE_NUMB_OEQL ) T2 ON T1.QUOTE_NUMBER=T2.QUOTE_NUMBER AND T1.SEQUENCE_NUMBER=T2.SEQUENCE_NUMBER AND T1.REVALIDATE_NUMB=T2.REVALIDATE_NUMB
WHERE ROUND(T1.TOTAL_SELL_PRC,2)<>ROUND(T2.TOTAL_SELL_PRC,2)
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

irudykCommented:
If this is being pulled from a SQL Server you could use PARTITION BY to create a field for each row record that would contain the total value you are looking for a particular QUOTE_NUMBER

Something like

SELECT T1.QUOTE_NUMBER,T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB,T1.TOTAL_SELL_PRC,T2.TOTAL_SELL_PRC
,SUM(T2.TOTAL_SELL_PRC) OVER (PARTITION BY T1.QUOTE_NUMBER) AS CSTQUTLN_TOT_SELL_PRC
FROM (
SELECT QUOTE_NUMBER_OEQH AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQH AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQH AS REVALIDATE_NUMB,TOTAL_SELL_PRC_OEQH AS TOTAL_SELL_PRC
FROM CSTQUTHD ) T1
INNER JOIN (
SELECT QUOTE_NUMBER_OEQL AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQL AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQL AS REVALIDATE_NUMB,SUM(QUOTE_QUANTITY_OEQL*QUOTE_SELL_PRIC_OEQL) AS TOTAL_SELL_PRC
FROM CSTQUTLN
GROUP BY QUOTE_NUMBER_OEQL,SEQUENCE_NUMBER_OEQL,REVALIDATE_NUMB_OEQL ) T2 ON T1.QUOTE_NUMBER=T2.QUOTE_NUMBER AND T1.SEQUENCE_NUMBER=T2.SEQUENCE_NUMBER AND T1.REVALIDATE_NUMB=T2.REVALIDATE_NUMB
WHERE ROUND(T1.TOTAL_SELL_PRC,2)<>ROUND(T2.TOTAL_SELL_PRC,2)

Open in new window


You would then reference the additional field (no formula required): CSTQUTLN_TOT_SELL_PRC
rwheeler23Author Commented:
The combination of quote,sequence and revalidate identify each document. I now need to update the CSTQUTHD.TOTAL_SELL_PRC_OEQH to the matching SUM(CSTQUTLN.QUOTE_QUANTITY_OEQL*QUOTE_SELL_PRIC_OEQL) value. What would the update statement look like? We have hundreds of those of the headers records and less than 1/10th of 1% have this issue. I want to fix them first and then figure out what is causing the difference.

SELECT T1.QUOTE_NUMBER,T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB,T1.TOTAL_SELL_PRC,T2.TOTAL_SELL_PRC
,SUM(T2.TOTAL_SELL_PRC) OVER (PARTITION BY T1.QUOTE_NUMBER,      T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB) AS CSTQUTLN_TOT_SELL_PRC
FROM (
SELECT QUOTE_NUMBER_OEQH AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQH AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQH AS REVALIDATE_NUMB,TOTAL_SELL_PRC_OEQH AS TOTAL_SELL_PRC
FROM CSTQUTHD ) T1
INNER JOIN (
SELECT QUOTE_NUMBER_OEQL AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQL AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQL AS REVALIDATE_NUMB,SUM(QUOTE_QUANTITY_OEQL*QUOTE_SELL_PRIC_OEQL) AS TOTAL_SELL_PRC
FROM CSTQUTLN
GROUP BY QUOTE_NUMBER_OEQL,SEQUENCE_NUMBER_OEQL,REVALIDATE_NUMB_OEQL ) T2 ON T1.QUOTE_NUMBER=T2.QUOTE_NUMBER AND T1.SEQUENCE_NUMBER=T2.SEQUENCE_NUMBER AND T1.REVALIDATE_NUMB=T2.REVALIDATE_NUMB
WHERE ROUND(T1.TOTAL_SELL_PRC,2)<>ROUND(T2.TOTAL_SELL_PRC,2)
irudykCommented:
I'd think you would just reference that field as it has been already been calculated in your derived table T2. So you'd have the following:

SELECT T1.QUOTE_NUMBER,T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB,T1.TOTAL_SELL_PRC,T2.TOTAL_SELL_PRC
,SUM(T2.TOTAL_SELL_PRC) OVER (PARTITION BY T1.QUOTE_NUMBER, T1.SEQUENCE_NUMBER,T1.REVALIDATE_NUMB) AS CSTQUTLN_TOT_SELL_PRC
,T2.TOTAL_SELL_PRC AS CSTQUTHD_TOTAL_SELL_PRC_OEQH 
FROM (
SELECT QUOTE_NUMBER_OEQH AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQH AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQH AS REVALIDATE_NUMB,TOTAL_SELL_PRC_OEQH AS TOTAL_SELL_PRC
FROM CSTQUTHD ) T1
INNER JOIN (
SELECT QUOTE_NUMBER_OEQL AS QUOTE_NUMBER,SEQUENCE_NUMBER_OEQL AS SEQUENCE_NUMBER,REVALIDATE_NUMB_OEQL AS REVALIDATE_NUMB,SUM(QUOTE_QUANTITY_OEQL*QUOTE_SELL_PRIC_OEQL) AS TOTAL_SELL_PRC
FROM CSTQUTLN
GROUP BY QUOTE_NUMBER_OEQL,SEQUENCE_NUMBER_OEQL,REVALIDATE_NUMB_OEQL ) T2 ON T1.QUOTE_NUMBER=T2.QUOTE_NUMBER AND T1.SEQUENCE_NUMBER=T2.SEQUENCE_NUMBER AND T1.REVALIDATE_NUMB=T2.REVALIDATE_NUMB
WHERE ROUND(T1.TOTAL_SELL_PRC,2)<>ROUND(T2.TOTAL_SELL_PRC,2)

Open in new window


You would then reference the additional field (no formula required): CSTQUTHD_TOTAL_SELL_PRC_OEQH
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
rwheeler23Author Commented:
OK, I will attempt to turn this into an update statement. Most of the work is already done.
irudykCommented:
Okay. Not sure why it would need to be turned into an update statement since the result set that it returns should provide the information you would need. But, I'm not clear if you have more going on that I'm not aware of, so I'll leave it to your discretion (just thought I'd point it out to you).
rwheeler23Author Commented:
The value on the header record needs to be corrected. There are reports that are run that use only the header table. These then get compared to reports that run only off the detail records. The results need to be the same for both.
irudykCommented:
Okay, thanks for the clarification!
Doug BishopDatabase DeveloperCommented:
This should do the update:
UPDATE    HDR
SET       TOTAL_SELL_PRC_OEQH = UPD.TOTAL_SELL_PRC
FROM      CSTQUTHD HDR
INNER JOIN 
        (
        SELECT T1.QUOTE_NUMBER,
               T1.SEQUENCE_NUMBER,
               T1.REVALIDATE_NUMB,
               T1.TOTAL_SELL_PRC,
               T2.TOTAL_SELL_PRC,
               SUM(T2.TOTAL_SELL_PRC) OVER (PARTITION BY T1.QUOTE_NUMBER, T1.SEQUENCE_NUMBER, T1.REVALIDATE_NUMB) AS CSTQUTLN_TOT_SELL_PRC
        FROM
            (
            SELECT QUOTE_NUMBER_OEQH AS QUOTE_NUMBER,
                   SEQUENCE_NUMBER_OEQH AS SEQUENCE_NUMBER,
                   REVALIDATE_NUMB_OEQH AS REVALIDATE_NUMB,
                   TOTAL_SELL_PRC_OEQH AS TOTAL_SELL_PRC
            FROM   CSTQUTHD
            ) T1
        INNER JOIN
            (
            SELECT QUOTE_NUMBER_OEQL AS QUOTE_NUMBER,
                   SEQUENCE_NUMBER_OEQL AS SEQUENCE_NUMBER,
                   REVALIDATE_NUMB_OEQL AS REVALIDATE_NUMB,
                   SUM(QUOTE_QUANTITY_OEQL * QUOTE_SELL_PRIC_OEQL) AS TOTAL_SELL_PRC
            FROM   CSTQUTLN
            GROUP BY QUOTE_NUMBER_OEQL,
                     SEQUENCE_NUMBER_OEQL,
                     REVALIDATE_NUMB_OEQL
            ) T2
        ON    T1.QUOTE_NUMBER = T2.QUOTE_NUMBER
        AND   T1.SEQUENCE_NUMBER = T2.SEQUENCE_NUMBER
        AND   T1.REVALIDATE_NUMB = T2.REVALIDATE_NUMB
        WHERE ROUND(T1.TOTAL_SELL_PRC, 2) <> ROUND(T2.TOTAL_SELL_PRC, 2)
        ) UPD
ON     HDR.QUOTE_NUMBER_OEQH = UPD.QUOTE_NUMBER
AND    HDR.SEQUENCE_NUMBER_OEQH = UPD.SEQUENCE_NUMBER
AND    HDR.REVALIDATE_NUMB_OEQH = UPD.REVALIDATE_NUMB;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Excellent. Thank you. From a performance perspective, is there any difference between doing this with partitions versus group by? I have always used group by but I see why partitioning works.
Doug BishopDatabase DeveloperCommented:
I like to use partitioning when I have multiple levels of grouping (which it allows you to do). Also, if you have a large column list and you are doing a SUM() on one of the columns, it typically makes for cleaner code. If you want to display 20 columns, but have a SUM() on Sales, and group on SalesDate, you don't have to have the other 19 columns in a GROUP BY statement. Using the window function and partitioning on only one column (SalesDate) is much more efficient to SQL Server than doing a GROUP BY on 20 columns.
rwheeler23Author Commented:
Thanks for your help folks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.