Solved

Selecting  specific bands of records in a table for update

Posted on 2013-12-15
8
342 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
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
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.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

791 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