Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Top n Records

Posted on 2008-06-23
4
Medium Priority
?
1,217 Views
Last Modified: 2008-06-23
Hello Experts,

I have a table (tblReg) that I wish to Update a field named "flag" to Yes. I only want to Update the TOP 100 records. I realize that the property Top Values is available only for a Select query not available for an Update query.

How can I simply Update this field from No to Yes with the criteria being only the first 100 records?
It does not matter which records, just 100 of them.

Thanks    
Jim
0
Comment
Question by:JimK31
[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
  • 2
4 Comments
 
LVL 75
ID: 21847436
Create a TOP 100 Select query first to limit the recs, then ... create a Update query that Joins on that query - so that only 100 recs will be affected.  Or, use a WHERE clause with the IN operator to 'join' on that query.

mx
0
 
LVL 11

Accepted Solution

by:
CMYScott earned 1000 total points
ID: 21847454
I'd try something like this...

(replace someField with a unique id column-name and orderField by the field you want to order by for the TOP)

UPDATE tblReg SET Flag=True WHERE tblReg.someField IN (SELECT TOP 100 someField FROM tblReg ORDER BY orderField)
0
 

Author Comment

by:JimK31
ID: 21847597
Thanks. Have not used a WHERE  IN statement. Thanks for spelling in out.
0
 
LVL 75
ID: 21847649

:-)
0

Featured Post

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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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…

722 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