How do I update n rows in a table?

Question

I need to update the first N rows in a table meeting a condition.

I know I can do an Update Top N... but the problem is that N is in a @variable.

UPDATE TOP @N SET ... doesn't work.

Is there a way to do this that I am just missing?

No specific table definitions here because it doesn't matter what the columns are.. If I can do it for a one column table I can do it for my table.

Answer 1

You need to use parens after TOP clause when you want to use a variable:

UPDATE TOP(@N) ...

Answer 2

WITH    q AS
        (
        SELECT  TOP (@r) *
        FROM    mytable
        ORDER BY
                col1
        )
UPDATE  q
SET     co12 = @value

UPDATE TOP (@r) will work but it will update any @r rows in no particular order.

From the documentation:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order. TOP n returns n random rows.