Update top N rows with N specific for each row from other table

Question

I Would like to perform update foreach row of some external table with its own update specific values and force it to update top (n) rows with using row from external table as CountToUpdate.

UPDATE TOP(q.CountToUpdate) dbo.TableName SET SomeId = NULL 
FROM @Quantities q 
WHERE TableName.Status = 960 AND TableName.SomeId = q.SomeId;

Is it possible to update in this manner or should I rewrite it to something other?

db on sql server 2012, Thank you

Answer 1

I would do this using row_number() and a join:

UPDATE t
    SET SomeId = NULL 
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.SomeId ORDER BY t.SomeId) as seqnum
      FROM dbo.TableName t
      WHERE t.Status = 960
     ) t JOIN
     @Quantities q 
     ON q.SomeId = ie.SomeId;

Answer 2

TOP without an order by is not deterministic, also when using TOP(n), n must be an outer reference. You can perform your update using CROSS APPLY, which allows both TOP and a deterministic order to be used:

UPDATE  t
SET     SomeID = NULL
FROM    @Quantities AS q
        CROSS APPLY
        (   SELECT  TOP (q.CountToUpdate) t.ID, t.SomeID, t.Status
            FROM    dbo.TableName AS t
            WHERE   t.SomeID = q.SomeID
            AND     t.Status = 960
            ORDER BY t.ID
        ) AS t;

Full working example:

DECLARE @T TABLE (ID INT IDENTITY, SomeID INT, Status INT);
DECLARE @Q TABLE (SomeID INT, CountToUpdate INT);

INSERT @T (SomeID, Status) 
VALUES 
    (1, 960), (1, 960), (1, 960), (1, 960), (1, 960),
    (2, 960), (2, 960), (2, 960), (2, 960), (2, 960),
    (3, 960), (3, 960), (3, 960), (3, 960), (3, 960);

INSERT @Q (SomeID, CountToUpdate)
VALUES (1, 4), (2, 3), (3, 2);

UPDATE  t
SET     SomeID = NULL
FROM    @q AS q
        CROSS APPLY
        (   SELECT  TOP (q.CountToUpdate) t.ID, t.SomeID, t.Status
            FROM    @T AS t
            WHERE   t.SomeID = q.SomeID
            AND     t.Status = 960
            ORDER BY t.ID
        ) AS t;

SELECT  *
FROM    @t;