Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.