Solved

Selecting  specific bands of records in a table for update

Posted on 2013-12-15
8
302 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
Comment Utility
<<
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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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.
Comment Utility
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
 

Author Comment

by:peispud
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I continue to improve with the help that the experts give me.  I value this greatly.

Thanks for all the help.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 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

10 Experts available now in Live!

Get 1:1 Help Now