Solved

UPDATE query with SELECT TOP x records from UNION

Posted on 2011-02-11
8
273 Views
Last Modified: 2012-05-11
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
Comment
Question by:wipnav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34874784
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
 
LVL 1

Author Comment

by:wipnav
ID: 34874927
Hmmm, I get the 'operation must use an updateable query' error
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34875113

can you upload a copy of the db
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 1

Author Comment

by:wipnav
ID: 34875285
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
 
LVL 5

Expert Comment

by:aternex
ID: 34878770
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
 
LVL 5

Expert Comment

by:aternex
ID: 34878784
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34882719
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
 
LVL 1

Author Closing Comment

by:wipnav
ID: 34884006
Thanks, splitting it up works perfectly.
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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