Will subquery (using IN) run multiple times for update statement (SQL Server 2017) 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?
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 &amp; multiple table accesses as a result of using NOT IN



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:



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)