[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I need help with a subquery in SQL Server 2008

Posted on 2014-08-25
6
Medium Priority
?
33 Views
Last Modified: 2016-04-01
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

0
Comment
Question by:mainrotor
4 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 1000 total points
ID: 40284214
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40284267
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
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 500 total points
ID: 40284831
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
 

Author Comment

by:mainrotor
ID: 40326823
I will try some of your suggestion and get back to you all.  Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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