Solved

Selecting  specific bands of records in a table for update

Posted on 2013-12-15
8
319 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 119

Accepted Solution

by:
Rey Obrero 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

932 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

8 Experts available now in Live!

Get 1:1 Help Now