Solved

Selecting  specific bands of records in a table for update

Posted on 2013-12-15
8
364 Views
Last Modified: 2013-12-22
I have a table with several thousand records.  There are 4 fields.
The key field is [ID] which auto-increments. The other fields will hold information for printing labels.  These three fields will be purged regularly.

I am using table this as a scratch table to print out batches of labels.  

I need to select the first 10 records in a table and apply an SQL update.
I may then need to be able to select records 11 - 35 and 36 - 200 and do the same.

Since the table can hold several thousand records, I would prefer to not step through this record by record.

I cannot use the key field because this field will only remain contiguous until someone deletes a record.

How can I select the first 10 records for such an update?  Then select records 11-35 etc?

Thank you
0
Comment
Question by:peispud
[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
8 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 39720638
<<
I cannot use the key field because this field will only remain contiguous until someone deletes a record.
>>

A  good reason to use a Y/N field called isDeleted instead of actually allowing deletions :-)


Give this a try (untested):

Create a query, and save it as qryRowNumber:

SELECT a.YourIDField,  (SELECT COUNT(*) FROM YourTable b WHERE b.YourIDField <= a.YourIDField) AS RowNumber
FROM YourTable a

Then use it in your updates like this:

UPDATE YourTable, qryRowNumber
SET YourTable.YourField1 = 123, YourTable.YourField2 = "abc" ... etc
WHERE YourTable.YourIDField =qryRowNumber.YourIDField AND qryRowNumber.RowNumber < 10

Open in new window


UPDATE YourTable, qryRowNumber
SET YourTable.YourField1 = 123, YourTable.YourField2 = "abc" ... etc
WHERE YourTable.YourIDField =qryRowNumber.YourIDField AND (qryRowNumber.RowNumber BETWEEN 11 AND 35)

Open in new window


etc.

If the row number looks correct but the update syntax gives you syntax issues, try turning qryRowNumber into a make table query and use the resultant table in place of qryRowNumber in the above update statements.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39720641
add a YesNo field to your table, name it Updated.

to update the first 10 records, run this query

update tablex
set [fieldname]="< some value>", updated=-1
where  [ID] In (select top 10 ID from tablex where updated=0)

on the next run, change 10 to 25 for records 11-35 and so on


when you aredone updating all records, run another update query to set all Updated field to 0

update tablex set updated=0
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39720668
How about:

SELECT top 15 DB_Lists.DB_ID, DB_Lists.DB_Name, DB_Lists.Server_Name, DB_Lists.Application
FROM DB_Lists
where DB_Lists.DB_ID >7
order by  DB_Lists.DB_ID

Open in new window

0
Independent Software Vendors: 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!

 

Author Comment

by:peispud
ID: 39720677
I'll wait some more for more answers.  I have found Capricorn1's reply to be well suited for my needs,  easily understood and implemented.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39720733
Curious what you found difficult in what I posted (did you try it)?  The idea there is to use a query to number the rows sequentially, and use the derived row numbers to pinpoint the records that need to be updated.
0
 

Assisted Solution

by:peispud
peispud earned 0 total points
ID: 39721141
In reply to Mbizup

I looked at it.   I did spend some time trying to understand.   Your advice has always been impeccably.

I had some trouble understanding though.

SELECT a.YourIDField,  (SELECT COUNT(*) FROM YourTable b WHERE b.YourIDField <= a.YourIDField) AS RowNumber
FROM YourTable a

I have a sense of what you had in mind but still unsure.  Your second post clears up any certainty of the the master plan, but the details (for me) still need to be worked out.

You also wrote the following.
A  good reason to use a Y/N field called isDeleted instead of actually allowing deletions :-)

I realize that I could disallow deletions to ensure sequential contiguous numbers for the key field [ID].     However, I do not wish to rely on such a precondition for my code to work.

Does your post rely on not allowing any deletions on the key field [ID]?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39725637
Hi - sorry for the slow reply.

<< Does your post rely on not allowing any deletions on the key field [ID]? >>

No - my remark about deletions was actually tangential.  In many of our production applications, we don't allow users to delete records, period (for a variety of reasons).  We might give them custom 'Delete' buttons which simply hide records as I described previously.

The rest of my comment not directly related to that remark, but rather compensates for missing IDs.  It assumes a set of data that does have gaps in its autonumber field (from deletions, corrupted records, etc), and the query orders the data by the autonumber field, and gives you a sequential 'row number' without the gaps that might be present in the autonumber field.

Hope that helps explain :-)
0
 

Author Closing Comment

by:peispud
ID: 39734473
I continue to improve with the help that the experts give me.  I value this greatly.

Thanks for all the help.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

696 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