Read uncommited does not work in UPDATE WHERE NOT EXISTS

Question

I'm not able to force SQL Server (2016) to respect values updated in the same statement.

What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) to new ones (column new_a in table #u)

After the update rows containing old values would be deleted.

Here is the demo code:

DROP TABLE IF EXISTS #t
DROP TABLE IF EXISTS #u

CREATE TABLE #t (a int, b int)

CREATE UNIQUE CLUSTERED INDEX t_idx ON #t
(
    a ASC,
    b ASC
)

CREATE TABLE #u (old_a int, new_a int)

INSERT INTO #t (a, b)
VALUES
(1,1),
(1,2),
(2,1),
(2,2)


INSERT INTO #u (old_a, new_a)
VALUES
(1, 3),
(2, 3)

UPDATE t
SET t.a = u.new_a
FROM #t AS t WITH (READUNCOMMITTED)
JOIN #u AS u
ON u.old_a = t.a

WHERE NOT EXISTS (
    SELECT 1
    FROM #t AS t_top WITH (READUNCOMMITTED)
    WHERE 1=1
    AND t_top.a = u.new_a
    AND t_top.b = t.b)

which results in

Cannot insert duplicate key row in object 'dbo.#t' with unique index 't_idx'. The duplicate key value is (3, 1).

And while I can do it other ways (eg. disable unique index, then delete duplicates) I would like to know why SQL Server does not want to read freshly updated values.

Also, is it possible to somehow update with commits after each row? Preferably without using stored procedures.

SQL Server version is 2016

EDIT: SQL Server sees the values when checking for FK violations, but not in WHERE EXISTS clause

EDIT 2:

Answer 1

There is no "first update", "second update" here, there is one statement, one atomic update. And this update produces 2 rows that are the same.

If you want "the second" update to not succeed you should write 2 explicit updates, not one, inside 1 transaction. But in this case nolock would have no sense because the same transaction sees its own uncommitted values without any hint at all.

Answer 2

On the contrary .. I think your database server DOES read the updated values...

to elaborate: simply visualize (if needed on a piece of paper), what happens during your update

before 
(1,1),(1,2),(2,1),(2,2)
1st update ->
(3,1),(1,2),(2,1),(2,2)
2nd update ->
(3,1),(3,2),(2,1),(2,2)
3rd update ->
(3,1),(3,2),(3 ***,1),(2,2)

If I understand your update statement correctly - the first line of #t will be (3,1) after first step - and then in the third run the 3rd entry of #t will become (3,1) too - which your combined unique index doesn't allow (visualized by *** in above lines)

It's common practice for your and similar cases to disable constraints during table spanning updates - and enabling them again afterwards.

But if I see your example table #u .. you won't be able to .. because you have data in the table that violates your constraint.. please check #u

Example

(3,1),
(4,2)

should work

EDIT:

WHERE NOT EXISTS (
    SELECT 1
    FROM #t AS t_top WITH (READUNCOMMITTED)
    WHERE 1=1
    AND t_top.a = u.new_a
    AND t_top.b = t.b)

should give WHERE 1 which evaluates as true to elaborate, the where condition of your select becomes false making the select deliver 0 or false (1=1 AND 2=3 AND 1=1) - if I put in the values for entry#3 of #t - ... which is negated by NOT EXISTS so your update is done ... and bang conflicting unique index