Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

UPDATE query with SELECT TOP x records from UNION

I have the following query below that I am trying to execute but does not seem to be working properly.

The query from "SELECT TOP 150 ReceiptKey FROM" line and down executes perfectly in under 1 second, but once I add the update statement, my computer goes haywire and is running at 100% CPU with no results even after minutes of processing.

I'm guessing something strange is happening, but I'm not quite sure why.

Also, "SortReceipts()" is a VBA function that I wrote that returns a string value back to my query

UPDATE tblOpenReceipts SET Reworked = 'TEST' WHERE ReceiptKey IN(

SELECT TOP 150 ReceiptKey FROM 

(SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) AS SortField 
FROM tblOpenReceipts INNER JOIN tblSpecialAssignment
ON tblOpenReceipts.SpecialAssignment = tblSpecialAssignment.Assignment
WHERE EmpID = 'Stmarine' AND TimeClosed IS NULL 

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts INNER JOIN tblDCToFaxRegion
ON LEFT(tblOpenReceipts.BankAcctNum,3) = tblDCToFaxRegion.ServingLocation
WHERE PaymentMethod = 'JV STORE IMPORT' AND TimeClosed IS NULL 
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL AND FaxInt = 1  

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts 
WHERE PaymentMethod LIKE '*AUTOLKBOX' AND TimeClosed IS NULL
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL 
ORDER BY SpecialAssignment DESC, GLDate ASC, SortField ASC))

Open in new window

0
wipnav
Asked:
wipnav
  • 3
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try
UPDATE tblOpenReceipts T
Inner Join

(SELECT TOP 150 ReceiptKey FROM 

(SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) AS SortField 
FROM tblOpenReceipts INNER JOIN tblSpecialAssignment
ON tblOpenReceipts.SpecialAssignment = tblSpecialAssignment.Assignment
WHERE EmpID = 'Stmarine' AND TimeClosed IS NULL 

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts INNER JOIN tblDCToFaxRegion
ON LEFT(tblOpenReceipts.BankAcctNum,3) = tblDCToFaxRegion.ServingLocation
WHERE PaymentMethod = 'JV STORE IMPORT' AND TimeClosed IS NULL 
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL AND FaxInt = 1  

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts 
WHERE PaymentMethod LIKE '*AUTOLKBOX' AND TimeClosed IS NULL
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL 
ORDER BY SpecialAssignment DESC, GLDate ASC, SortField ASC))) T2
On T.ReceiptKey=T2.ReceiptKey

SET Reworked = 'TEST'

Open in new window

0
 
wipnavAuthor Commented:
Hmmm, I get the 'operation must use an updateable query' error
0
 
Rey Obrero (Capricorn1)Commented:

can you upload a copy of the db
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wipnavAuthor Commented:
Let me see if I can, I would have to strip a lot of data out before I did. I will have to get back to it later tonight.
0
 
aternexCommented:
Looks like there might just be a syntax error in capricorn's solution. Try this.
UPDATE T
SET T.Reworked = 'TEST' 
FROM tblOpenReceipts 
  Inner Join
(SELECT TOP 150 ReceiptKey FROM 

(SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) AS SortField 
FROM tblOpenReceipts INNER JOIN tblSpecialAssignment
ON tblOpenReceipts.SpecialAssignment = tblSpecialAssignment.Assignment
WHERE EmpID = 'Stmarine' AND TimeClosed IS NULL 

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts INNER JOIN tblDCToFaxRegion
ON LEFT(tblOpenReceipts.BankAcctNum,3) = tblDCToFaxRegion.ServingLocation
WHERE PaymentMethod = 'JV STORE IMPORT' AND TimeClosed IS NULL 
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL AND FaxInt = 1  

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts 
WHERE PaymentMethod LIKE '*AUTOLKBOX' AND TimeClosed IS NULL
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL 
ORDER BY SpecialAssignment DESC, GLDate ASC, SortField ASC))) T2
On T.ReceiptKey=T2.ReceiptKey

Open in new window

0
 
aternexCommented:
Try both T.Reworked and just Reworked as well. T.Reworked might give you another syntax error... I can't remember if thats valid, but out of force of habit I always type queries that way first. Sorry for the confusion.
0
 
Rey Obrero (Capricorn1)Commented:
save the union query

SELECT TOP 150 ReceiptKey FROM 

(SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) AS SortField 
FROM tblOpenReceipts INNER JOIN tblSpecialAssignment
ON tblOpenReceipts.SpecialAssignment = tblSpecialAssignment.Assignment
WHERE EmpID = 'Stmarine' AND TimeClosed IS NULL 

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts INNER JOIN tblDCToFaxRegion
ON LEFT(tblOpenReceipts.BankAcctNum,3) = tblDCToFaxRegion.ServingLocation
WHERE PaymentMethod = 'JV STORE IMPORT' AND TimeClosed IS NULL 
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL AND FaxInt = 1  

UNION ALL

SELECT ReceiptKey, SpecialAssignment, GLDate, SortReceipts(ReceiptNumber, ReceiptBatch) 
FROM tblOpenReceipts 
WHERE PaymentMethod LIKE '*AUTOLKBOX' AND TimeClosed IS NULL
AND SpecialAssignment IS NULL AND TimeAssigned IS NULL 
ORDER BY SpecialAssignment DESC, GLDate ASC, SortField ASC)

Open in new window



as qryTOP100


then try this query

UPDATE tblOpenReceipts 
Inner Join qryTOP100 ON
tblOpenReceiptsReceiptKey=qryTOP100.ReceiptKey
SET Reworked = "TEST"

Open in new window

0
 
wipnavAuthor Commented:
Thanks, splitting it up works perfectly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now