I need help with a subquery in SQL Server 2008

Hi Experts,
I have the following query, which is suppose to update 3 fields in one of my tables.  It appears that the MISC, and the ORMISC fields get updated just fine, but the DOCAMNT field does get update with the sum from the sub query.  
It's probrably because at the time my subquery runs, it probably does not know that MISC field has been updated with the value '3.00'  (see query below).

What can I do to fix this?  So the DOCAMNT field is updated with the '3.00' included in it?  Thanks in advance -- MROTOR

UPDATE A
SET MISC = '3.00', 
ORMISC = '3.00',
DOCAMNT = B.[ORDERTOTAL]
--,ORDOCAMT = B.ORDERTOTAL 		
FROM TORDER A  INNER JOIN
(SELECT CUSTNMBR, ORDNUMBER,SUM(SUBTOTAL + TRDISAMT + FRTAMNT + MISC + TAXAMNT) AS [ORDERTOTAL]
FROM TORDER
WHERE (CUSTNMBR LIKE 'ET-%' OR CUSTNMBR LIKE 'ETF-%')
AND ORDTYPE = '3'
AND INVODATE > (GETDATE() - 1)
GROUP BY CUSTNMBR, ORDNUMBER
) B
ON A.CUSTNMBR = B.CUSTNMBR and A.ORDNUMBER = B.ORDNUMBER
WHERE (A.CUSTNMBR LIKE 'ET-%' OR A.CUSTNMBR LIKE 'ETF-%')
AND A.ORDTYPE = '3'
AND A.INVODATE > (GETDATE() - 1)

Open in new window

mainrotorAsked:
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.

Kyle AbrahamsSenior .Net DeveloperCommented:
Can you throw it in a temp table?

SELECT CUSTNMBR, ORDNUMBER,SUM(SUBTOTAL + TRDISAMT + FRTAMNT + MISC + TAXAMNT) AS [ORDERTOTAL]
into #temp
FROM TORDER
WHERE (CUSTNMBR LIKE 'ET-%' OR CUSTNMBR LIKE 'ETF-%')
AND ORDTYPE = '3'
AND INVODATE > (GETDATE() - 1)
GROUP BY CUSTNMBR, ORDNUMBER

--validate results:
select * from #temp

UPDATE A
SET MISC = '3.00',
ORMISC = '3.00',
DOCAMNT = B.[ORDERTOTAL]
FROM TORDER A  INNER JOIN
#temp B
ON A.CUSTNMBR = B.CUSTNMBR and A.ORDNUMBER = B.ORDNUMBER
WHERE (A.CUSTNMBR LIKE 'ET-%' OR A.CUSTNMBR LIKE 'ETF-%')
AND A.ORDTYPE = '3'
AND A.INVODATE > (GETDATE() - 1)
0

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
Scott PletcherSenior DBACommented:
I suggest replacing the MISC column in the sum/total computation with a literal 3.00 instead, since you know that will be the value it will have when the UDPATE is complete:


UPDATE A
SET MISC = '3.00',
ORMISC = '3.00',
DOCAMNT = B.[ORDERTOTAL]
--,ORDOCAMT = B.ORDERTOTAL             
FROM TORDER A  INNER JOIN
(SELECT CUSTNMBR, ORDNUMBER,SUM(SUBTOTAL + TRDISAMT + FRTAMNT + 3.00 + TAXAMNT) AS [ORDERTOTAL]
FROM TORDER
WHERE (CUSTNMBR LIKE 'ET-%' OR CUSTNMBR LIKE 'ETF-%')
AND ORDTYPE = '3'
AND INVODATE > (GETDATE() - 1)
GROUP BY CUSTNMBR, ORDNUMBER
) B
ON A.CUSTNMBR = B.CUSTNMBR and A.ORDNUMBER = B.ORDNUMBER
WHERE (A.CUSTNMBR LIKE 'ET-%' OR A.CUSTNMBR LIKE 'ETF-%')
AND A.ORDTYPE = '3'
AND A.INVODATE > (GETDATE() - 1)
0
DcpKingCommented:
Just do it in two queries:

UPDATE A
    SET MISC = '3.00', 
        ORMISC = '3.00'

UPDATE A
SET DOCAMNT = B.[ORDERTOTAL]
FROM TORDER A  INNER JOIN
(SELECT CUSTNMBR, ORDNUMBER,SUM(SUBTOTAL + TRDISAMT + FRTAMNT + MISC + TAXAMNT) AS [ORDERTOTAL]
FROM TORDER
WHERE (CUSTNMBR LIKE 'ET-%' OR CUSTNMBR LIKE 'ETF-%')
AND ORDTYPE = '3'
AND INVODATE > (GETDATE() - 1)
GROUP BY CUSTNMBR, ORDNUMBER
) B
ON A.CUSTNMBR = B.CUSTNMBR and A.ORDNUMBER = B.ORDNUMBER
WHERE (A.CUSTNMBR LIKE 'ET-%' OR A.CUSTNMBR LIKE 'ETF-%')
AND A.ORDTYPE = '3'
AND A.INVODATE > (GETDATE() - 1)

Open in new window


because, as you say, the operation is atomic, so the misc and ormisc values cannot be assumed to have changed before the execution of the subquery.

hth

Mike
0
mainrotorAuthor Commented:
I will try some of your suggestion and get back to you all.  Thanks.
0
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.