Update a field for a specific # of records in SQL Server 2005 -

say want 3 records flagged each product in table. if products 1 or 2 records flagged or no records flagged, how can make randomly flag remaining records total of 3 per product.

ex:

1 record gets flagged product_a, 2 records flagged product_b , 3 records flagged product_c.

once script complete, need 2 more records flagged product_a , 1 more product_b.

this can loop or cte or whatever efficient way in sql. thanks!

here's 1 way it:

;with selectedids as(   select      id,     row_number() on (       partition productcode -- distinct numbering each product code       order newid() -- random     ) rowno   productlines ) update p   set isflagged = 1 productlines p join selectedids s   on p.id = s.id ,       s.rowno <= 3 -- limit 3 records / product code ; 

here's full sample, including test data: http://www.sqlfiddle.com/#!3/3bee1/6