Reorder records in table

I am looking for a SQL solution to reorder records by updating order column.

Consider following data structure:

------------------------------------
| id | type | value | order | user |
------------------------------------
|  1 |    A |   123 |     1 |    1 |
|  2 |    B |   231 |     2 |    1 |
|  3 |    B |   213 |     3 |    2 |
|  4 |    A |   222 |     4 |    2 |
|  5 |    A |    22 |     5 |    3 |
|  6 |    B |    11 |     6 |    4 |
|  7 |    C |    99 |     7 |    1 |
------------------------------------

Software(non-modifiable) that uses this data, takes order in account and applies value with max order for each user.
However I need that type A records are applied first (have max order for particular users entries).
Order column is unique.

Current solution is to create temporary table, selecting data in desired order and adding new identify column. Effectively rebuilding whole order column. Like this:

BEGIN TRANSACTION;

-- first we take type C
SELECT id
    ,type
    ,value
    ,[ORDER]
    ,user
INTO #tmp
FROM records
WHERE type = C;

-- we add new identify column
ALTER TABLE #tmp ADD new_order INT identify (1,1);

INSERT INTO #tmp
SELECT id
    ,type
    ,value
    ,[ORDER]
    ,user
FROM records
WHERE type = B;

-- last we take type A, these will have largest `order` values
INSERT INTO #tmp
SELECT id
    ,type
    ,value
    ,[ORDER]
    ,user
FROM records
WHERE type = A;

-- old update method
--update records set records.order = #tmp.new_order from records
--join #tmp on #tmp.id = records.id
COMMIT TRANSACTION;

-- new method thanks @Lennart
MERGE INTO records x
USING #tmp
    ON #tmp.id = records.id
WHEN MATCHED
    THEN
        UPDATE
        SET records.
        ORDER = #tmp.new_order;

DROP TABLE #tmp;

However this solution locks the table for too long, which is not acceptable during business hours.

Is it possible and effective to split this per user?

-
Take all records of user 1

-
Reorder using only order values of gi

Table

CREATE TABLE dbo.Records
(
    id integer NOT NULL
        CONSTRAINT [PK dbo.Records id]
        PRIMARY KEY CLUSTERED (id),
    [type] character(1) NOT NULL,
    [value] integer NOT NULL,
    [order] smallint NOT NULL,
    [user] integer NOT NULL,

    -- order is unique
    CONSTRAINT [UQ dbo.Records order]
        UNIQUE NONCLUSTERED ([order] DESC)
);

Indexes

-- type is unique per user, useful to include order
CREATE UNIQUE INDEX [UQ dbo.Records user, type (order)]
ON dbo.Records ([user], [type])
INCLUDE ([order]);

-- useful index on user, order
CREATE NONCLUSTERED INDEX [UQ dbo.Records user, order]
ON dbo.Records ([user] ASC, [order] DESC);

Sample data

INSERT dbo.Records
    (id, [type], [value], [order], [user])
VALUES
    (1, 'A', 123, 1, 1),
    (2, 'B', 231, 2, 1),
    (3, 'B', 213, 3, 2),
    (4, 'A', 222, 4, 2),
    (5, 'A',  22, 5, 3),
    (6, 'B',  11, 6, 4),
    (7, 'C',  99, 7, 1);

Solution

The following is similar to other answers in outline (joining two sets of numbered rows), but it optimizes the join (avoiding nested loops), and avoids changing rows where the order is not changed by the application of the algorithm. Comments inline:

WITH
    Existing AS
    (
        -- Number existing rows in order, per user
        SELECT
            R.[user],
            R.[order],
            rn = ROW_NUMBER() OVER (
                PARTITION BY R.[user] 
                ORDER BY R.[order] DESC)
        FROM dbo.Records AS R 
    ),
    New AS
    (
        -- Number existing rows by type, per user
        SELECT
            R.[user],
            R.[order], 
            rn = ROW_NUMBER() OVER (
                PARTITION BY R.[user] 
                ORDER BY R.[type] ASC)
        FROM dbo.Records AS R 
    ),
    OptNew AS
    (
        -- Help the optimizer see that New.rn is unique per user
        SELECT
            New.[user],
            [order] = MAX(New.[order]), -- Meaningless, but required
            New.rn
        FROM New
        GROUP BY 
            New.[user], 
            New.rn
    )
UPDATE E
SET [order] = N.[order]
FROM Existing AS E
JOIN OptNew AS N
    ON N.[user] = E.[user]
    AND N.rn = E.rn
WHERE
    -- Only update if changed
    N.[order] <> E.[order];

db<>fiddle

Execution plan

StackExchange Database Administrators Q#182659, answer score: 3