0

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

3

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.

  • I think you're right in the first part... About second part: I've got a lot of records to map to newer ID, so this way won't work – dreptak Nov 7 at 17:20
  • Just make sure that your mapping table #u doesnt contain conflicting data – eagle275 Nov 8 at 12:04
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

  • The 3rd update you described should be excluded by WHERE NOT EXISTS clause - it should check if tuple (3,1) exists in table, and proceed with update only if it does not – dreptak Nov 7 at 16:01
  • 1
    Nope ... if my brain serves right ... the update is applied AFTER the where condition is checked (to find the correct entry for update). – eagle275 Nov 7 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.