1

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?

1

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.

  • Thanks Jon. That did the trick! I realized soon after I posted the question too that perhaps I was not in the best place to be asking this.. :P – SlowFamily Nov 7 '13 at 0:42

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.