Solved

Update Top n Records

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a Temp Table in MS Access 5 44
Saving data on a subform when I close the form 12 30
Linking Master Child Fields 2 25
get and set file atrributes 5 10
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…
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.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

756 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