SQL Server – Will Subquery Run Multiple Times for Update Statement?

sql serversql-server-2017subqueryupdate

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?

Best Answer

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

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)

Related Solutions

Sql-server – SQL Subquery , One to Multiple rows

You will have to include DG in your grouping...

Select Consignment_id
       ,Orders_boxsize
       ,Orders_boxweight
       ,Orders_boxlength
       ,Orders_boxwidth
       ,Orders_boxdepth
       ,Order_dispatchment_id
       ,SUM(Component_weight) as Component_weight
       ,SUM(_ic) as _ic
       ,DG
from
(       
SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
,(
               SELECT CASE 
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG       
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id
) a
Group by
Consignment_id
,Orders_boxsize
,Orders_boxweight
,Orders_boxlength
,Orders_boxwidth
,Orders_boxdepth
,Order_dispatchment_id
,DG

Sql-server – How to update where subquery value is null in SQL Server

Many of your NULLs may be excluded by the fact that you're using an INNER JOIN between the tables. Try flipping it around:

UPDATE C
SET PriceClassID = 'A'
FROM    Customer AS C
    LEFT JOIN
        (
        SELECT CustId, SUM(curybaldue) AS last_sum
        FROM SOShipHeader
        WHERE OrdDate > @year_ago
            AND Status = 'C'
        GROUP BY CustID
        ) AS A ON C.CustId = A.CustId
WHERE
        (A.last_sum < 3000 OR A.last_sum IS NULL)
    AND C.PriceClassID IN ('CLUB', 'CLUB-E')
    AND C.Customer.User7 <= @year_ago
    AND C.Customer.User7 > @year_and_month_ago

Incidentally, relatively simple queries like this are usually better on StackOverflow; DBA.SE is meant more for server administration, database design, performance tuning, and such.

Best Answer

Related Solutions

Sql-server – SQL Subquery , One to Multiple rows

Sql-server – How to update where subquery value is null in SQL Server

Related Question