0

Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?

UPDATE a 
SET DM = 1
FROM TableA a 
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA WHERE DM = 1) 

Column ID is not unique in the table so we can have multiple rows for an ID

Will it always update all rows for the ID to DM = 1 or sometime just one?

  • 1
    Why not use UPDATE ... WHERE DM <> 1? – McNets Feb 6 at 14:11
  • I want to update only where there are none for the ID already set to 1. There are multiple rows for that ID – Dale Feb 6 at 14:16
2

Will it always update all rows for the ID to DM = 1 or sometime just one?

If you don't have NULL values in the ID column then either all DM's for one type of ID != 1 and as a result all ID's that have the same value will be updated or none.

But as said previously, depending on nullability of the ID column you can have suprising results and performance implications.

More on NOT IN and NOT EXISTS here.

An example of spools being added & multiple table accesses as a result of using NOT IN

enter image description here

A cleaner way to write your query:

UPDATE a 
SET a.DM = 1
FROM TableA a
WHERE NOT EXISTS 
(
SELECT ID FROM TableA  b
WHERE b.DM = 1
AND b.ID = a.id );

Do note that NULL values will be updated as well, you would have to add extra logic such as adding AND a.id IS NOT NULL;.

Resulting cleaner join path:

enter image description here

Example of null values resulting in 0 updates happening:

CREATE TABLE dbo.TableA (id int,DM int);

INSERT INTO dbo.TableA(id,DM)

SELECT TOP 500 
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) %200, 1
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.TableA(id,DM)

SELECT TOP 500 
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) +200  , 0
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.TableA(id,DM)
VALUES(NULL,1),(NULL,0);

UPDATE a 
SET DM = 1
FROM TableA a 
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA WHERE DM = 1) ;

--> 0 rows affected

UPDATE a 
SET a.DM = 1
FROM TableA a
WHERE NOT EXISTS 
(
SELECT ID FROM TableA  b
WHERE b.DM = 1
AND b.ID = a.id )
AND a.id IS NOT NULL; --If you do not want to update null id's

--> (500 rows affected)

  • Thanks for your answer and interesting info re NULL values using IN. Was just curious if there would ever be a case where the subquery would be run for each row (from your post I assume not) – Dale Feb 6 at 15:11
  • @Dale - When the anti semi join uses nested loops then part of the subquery would run for each row – Martin Smith Feb 6 at 16:49

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.