SQL Server – Read Uncommitted Not Working in UPDATE WHERE NOT EXISTS

isolation-levelsql serversql-server-2016update

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:
Image of how I was hoping this would work

Best Answer

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.

Related Question