Have Geek Brain

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

I need a way to get the result set from KeyID and split it as evenly as possible and update records differently for each OU based on KeyID. In other words, there is

SELECT KeyID
FROM   TableA
WHERE (some criteria exists)

I want to update TableA in 3 different ways with 3 equal parts of KeyID.

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 like that. Thanks for any of your answers.

+2


a source to share


6 answers 


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


+4


a source


Unfortunately I don't have time to knock out 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 split into 3 groups. then join this CTE in an UPDATE statement and execute the CASE statement in the NTILE group to determine whether to use Value1, Value2, or Value3.



Edit See Thomas's answer for the code for this as it has the same idea!

+2


a source


For easy distribution, create a random ranking and mod 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 ORDER BY for ROW_NUMBER () or use NTILE and remove modulo

+1


a source


If the keys are evenly distributed, you can use the ( %

) module to select 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;
+1


a source


By interpreting what you say literally, you can number the lines in the set of returned lines and then select different segments based on their line number.

eg.

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)
0


a source 


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

Please note that you can change the sentence ORDER BY

in the request to any valid expression that will allow you to determine your "first third" according to any criteria.

0


a source






More articles:


All Articles