Solved

UPDATE query with SELECT TOP x records from UNION

Posted on 2011-02-11
8
272 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
Independent Software Vendors: 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!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

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