SQL Server / T-SQL : How to update equal percentages of a resultset?

Question

I need a way to take a resultset of KeyIDs and divide it up as equally as possible and update records differently for each division based on the KeyIDs. In other words, there is

SELECT KeyID
FROM   TableA
WHERE (some criteria exists)

I want to update TableA 3 different ways by 3 equal portions of KeyIDs.

UPDATE TableA
SET    FieldA = Value1
WHERE  KeyID IN (the first 1/3 of the SELECT resultset above)

UPDATE TableA
SET    FieldA = Value2
WHERE  KeyID IN (the second 1/3 of the SELECT resultset above)

UPDATE TableA
SET    FieldA = Value3
WHERE  KeyID IN (the third 1/3 of the SELECT resultset above)

or something to that effect. Thanks for any and all of your responses.

Answer 1

Interpreting what you say literally, you could number the rows in the returned row set, and then select the different segements based on their row number.

E.g.

UPDATE TableA
SET    FieldA = Value1
WHERE  KeyID IN (SELECT * FROM (SELECT <your rows>, ROW_NUMBER() (ORDER BY <anyRow>) AS RowNumber FROM <yourTable> ) base
WHERE RowNumber<Count(RowNumber)/3)

UPDATE TableA
SET    FieldA = Value1
WHERE  KeyID IN (SELECT * FROM (SELECT <your rows>, ROW_NUMBER() (ORDER BY <anyRow>) AS RowNumber FROM <yourTable> ) base
WHERE RowNumber<Count(RowNumber)*2/3 && RowNumber>=Count(RowNumber)/3)

UPDATE TableA
SET    FieldA = Value1
WHERE  KeyID IN (SELECT * FROM (SELECT <your rows>, ROW_NUMBER() (ORDER BY <anyRow>) AS RowNumber FROM <yourTable> ) base
WHERE owNumber>=Count(RowNumber)*2/3)

Answer 2

For a simple distribution, create a random ranking and modulo by 3...

UPDATE
    A
SET
    FieldA =
        CASE Ranking % 3
           WHEN 1 THEN B.Value1
           WHEN 2 THEN B.Value2
           WHEN 0 THEN B.Value3
        END
FROM
    TableA A
    inner join
    (SELECT
        ID,
        ROW_NUMBER() OVER (ORDER BY ID /*or something*/) AS Ranking,
        Value1, Value2, Value3
    FROM
        TableA
    ) B on A.ID = B.ID
where (some criteria exists)

You can change the ORDER BY for the ROW_NUMBER(), or use NTILE and remove the modulo

Answer 3

Unfortunately I haven't got time to knock up a complete solution but the gist of one would be to use a CTE with the NTILE function http://msdn.microsoft.com/en-us/library/ms175126.aspx to divide into 3 groups then join onto that CTE in your UPDATE statement and do a CASE statement against the NTILE group to determine whether to use Value1, Value2, or Value3.

Edit See Thomas's answer for the code for this as looks like he had the same idea!

Answer 4

WITH Query (OtherKeyID, PCT)
AS
(
SELECT  KeyID, (ROW_NUMBER() OVER (ORDER BY KeyID)) / foo.CNT AS PCT 
FROM    TableA 
JOIN    (SELECT CONVERT(float, COUNT(1)) AS CNT FROM TableA) foo ON 1 = 1 
WHERE   (criteria)
)

UPDATE TableA  
SET    FieldA = (CASE
    WHEN PCT < .3333 THEN Value1 
    WHEN PCT BETWEEN .3333 and .6666 THEN Value2
    WHEN PCT > .6666 THEN Value3 ELSE NULL END)
FROM   Query 
WHERE  KeyID = OtherKeyID AND PCT < .3333 

Note that you can alter the ORDER BY clause in the query to any valid expression, which will allow you to define your "first third" by any criteria.

Answer 5

If the keys are evenly-distributed, then you could use the modulus (%) operator to select out unique thirds of the result set.

update TableA set FieldA = Value1 where KeyID % 3 = 0;
update TableA set FieldA = Value2 where KeyID % 3 = 1;
update TableA set FieldA = Value3 where KeyID % 3 = 2;

Answer 6

With TiledItems As
    (
        Select KeyId
            , NTILE(3) OVER( ORDER BY ... ) As NTileNum
        From TableA
        Where ...
    )
Update TableA
Set FieldA = Case TI.NTileNum
                    When 1 Then Value1
                    When 2 Then Value2
                    When 3 Then Value3
                    End
From TableA As A
    Join TiledItems As TI
        On TI.KeyId = A.KeyId