Update the first N rows with N specific for each row of another table

advertisements

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


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;