Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Selecting  specific bands of records in a table for update

Posted on 2013-12-15
8
Medium Priority
?
376 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 1000 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 1000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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