How to update and order by using ms sql

sql

subqueries

update-rows

sql-tricks

byAlex Kataev·Nov 19, 2024

Here is a quick and handy snippet to UPDATE rows in a specific order using a Common Table Expression (CTE):

-- Behold the magic of CTE!
WITH CTE AS (
    SELECT TOP(5) *
    FROM YourTable
    WHERE ConditionColumn = 'ConditionValue' -- Choose your battle
    ORDER BY OrderColumn ASC -- How you want it?
)
UPDATE CTE
SET UpdateColumn = 'NewValue' -- Here is where magic happens!

As you see, the first 5 rows where ConditionColumn matches 'ConditionValue', ordered by OrderColumn, receive new shine.

The power of subqueries

Subqueries are the real lifesavers when updating rows without the privilege of ORDER BY. They bolt down your target rows:

UPDATE YourTable
SET YourColumn = 'NewValue' -- New value in place
WHERE ID IN (
    SELECT TOP(10) ID
    FROM YourTable
    WHERE SomeCondition = True -- Condition "True" is always true, or is it? 😅
    ORDER BY PriorityColumn DESC
)

Notice, the power moves through the streets of IN-clause that helps you update only the targeted rows.

ORDER BY tricks: No unique key, no problem!

Ever faced an update scenario with no identity or unique columns, yet target the top N rows? Don't fret, subqueries come to the rescue:

UPDATE YourTable
SET YourColumn = 'NewValue' -- Updating without fear!
WHERE ID IN (
    SELECT ID
    FROM (
        SELECT ID, ROW_NUMBER() OVER (ORDER BY PriorityColumn DESC) as RowNum -- Descending into chaos!
        FROM YourTable
        WHERE StatusCode = 0 -- Zero tolerance zone!
    ) as SubQuery
    WHERE RowNum <= 10 -- Picks the top 10, who's the lucky one now?
)