?
Solved

UPDATE query with SELECT TOP x records from UNION

Posted on 2011-02-11
8
Medium Priority
?
275 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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 2000 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 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