troubleshooting Question

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

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server
11 Comments1 Solution81 ViewsLast Modified:
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)
ASKER CERTIFIED SOLUTION
D B
Database Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros