Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1221
  • Last Modified:

Update Top n Records

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
JimK31
Asked:
JimK31
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
CMYScottCommented:
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
 
JimK31Author Commented:
Thanks. Have not used a WHERE  IN statement. Thanks for spelling in out.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

:-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now