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

nullsql server

I am attempting to update a column if another value from a subquery is less than a certain value or null:

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

However, when I then check if the PriceClassID was changed, I see it was not changed for any row where last_sum was null. What am I doing wrong?

Best Answer

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.

Related Question