Update column in Top 20 records 1

[jadams0173]

I have a one time task that I have to do on about 10000 records. I'm selecting from a SQL 2000 table the top 20 records. I'm doing this with a query from VB6. After I've printed labels for the top 20 I ask the user if they printed OK. If the user selects YES then I need to set the Printed column to 1. Execute a requery of the RS VB and process the next 20. If the user says no then i take no action and exit the sub.

My question is how can update the same 20 records that I just processed? I can write a loop from VB to do this if the user selects YES or I can do it one record at a time from VB as I print the label and then undo my changes if the user selects NO using a loop.

How could I do this from SQL. Can I pass in a RS. I tried UPDATE Top 20.... but that creates and error.

The task isn't that huge but it's more of a learing experience than anything to do it with sql. I thought about Batch updates but I'm not sure how to implement them or if they would work for me.

 

[RiverGuy]

Can I pass in a RS

No, but you can create a string with your primary key values delimited by a specified character and use a function to turn that string into a table which you would use to update. Here's one example, but there are many examples on the web for this type of function. I haven't used this particular one, so I have no idea if it is the best.

http://blog.krisvandermast.com/UsingACommaDelimitedStringWithIdsAsInputParameterForASQLQuery.aspx

 

[jadams0173]

Thanks RiverGuy. I'll have a look at the function.

 

[bborissov]

UPDATE YourTable SET SomeField = 1
FROM YourTable
INNER JOIN (SELECT TOP 20 PrimeryKey 
                   FROM YourTable
            ORDER BY ??????) DrvTable
ON  YourTable.PrimaryKey = DrvTable.PrimeryKey

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jadams0173]

Thanks bborissov!

 

[SQLSister]

the order by is important make sure you use one or you will not always get the same twenty records.

"NOTHING is more important in a database than integrity." ESquared

 

[jadams0173]

Will do SQLSister.