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
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