update a certain percentage of records that meet a criteria

[link9]

Hello all --

Quick one for yas...

Say I have 100 records in a table that meet some criteria that I want to update by. How would I update only half of these to reflect a new value in one column???

For example:
I INSERT 100 new records in a table with a default value of 0 for a column called 'findMe'. After these INSERTS, I need to go back through those 100 records and assign 50% of the findMe columns to be equal to 1. My first shot at this was:

UPDATE TOP 50 tableName SET findMe = 1 WHERE dateReceived = '08/17/01'

But of course, that didn't work. RDBMS is SQL Server 7.0 if that helps any.

Thanks!
Paul Prewett

 

[tlbroadbent]

You could do this.

Set rowcount 50
UPDATE tableName SET FindMe = 1
WHERE dateReceived = '08/17/01'
AND FindMe=0
set rowcount 0

Of course, the limitation is that the first 50 records rather than a percentage will be updated. Also, it will be the first 50 records that meet the criteria. There will be no randomness the selection.

You could do a percentage this way.

declare @c int
Select @c=count(*)*.5 from tablename

set rowcount @c
UPDATE tableName SET FindMe = 1
WHERE dateReceived = '08/17/01'
AND FindMe=0
set rowcount 0
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[link9]

Yea, I was afraid that it would take a statement like that. Problem is that I'm working with the database via ADO, and so I needed one statement that would do it.... either that or use a SPROC (which would work with your solution), but I didn't want to do that --

That's ok, though. What I did was create a random array on the front end based on the specified percentage of records to INSERT -- and then as I'm inserting records, I check against the array, and if I find that record number, I assign a 1 -- if not, I leave it 0.

Works pretty well.

Thanks -
paul