Solved

Update Top n Records

Posted on 2008-06-23
4
1,196 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
  • 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 250 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now