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
Solved

UPDATE query with SELECT TOP x records from UNION

Posted on 2011-02-11
8
271 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access VBA Code to Include Library References 4 51
UK Date format in MS Access 4 22
Microsoft Access to CSV file from VBA 3 26
Copying an open file 3 21
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 utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 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