2
UPDATE #Product_Comments
  SET #Product_Comments.Comments = (
      SELECT COALESCE(STRING_AGG(p.Label, ', '), '') + ' '
      from (
        select distinct Label, Product_ID
        from #TEMPORARYTB
        where Product_ID = pc.Product_id
      ) as p  
      GROUP BY p.Product_ID
  ) + pc.Comments from #Product_Comments pc
JOIN Product_Property p ON p.Product_ID = pc.Product_id

I used COALESCE() when there was just STRING_AGG(p.Label, ', ') before, and I am still getting NULL inside comments. If I print Product_Comments before the update, I don't see any NULL inside comments.

SELECT *, '4' AS debug FROM #Product_Comments

I am printing it like this. So, when I do

SELECT *, '3' AS debug FROM #Product_Comments

I don't see any NULL, which is weird. It means that the COALESCE is not working somehow. How can I fix this?

3
  • + pc.Comments from #Product_Comments pc I have to say, I don't know how sql server handles this, because you don't link it back to the table you're updating. I would've expected an error, actually. Don't you mean + #Product_Comments.Comments? Commented Feb 7, 2022 at 20:13
  • pc.Comments from #Product_Comments pc is #Product_Comments.Comments, and it doesn't throw an error on my side.
    – Sayaman
    Commented Feb 7, 2022 at 20:22
  • If the subquery returns empty rowset then the value assigned will be NULL. You must COALESCE the whole subquery, not output column only. dbfiddle.uk/…
    – Akina
    Commented Feb 7, 2022 at 20:31

1 Answer 1

3

In addition to what Akina said about your subquery possibly returning an empty rowset, which will produce a NULL. You're also turning around and concatenating the result of the SELECT right back to a potential NULL with ) + pc.Comments from #Product_Comments pc.

If any part of the concatenate contains a NULL, the whole result becomes NULL. You need an additional COALESCE around the last reference to pc.Comments.

Try this and see if it works. It's pretty rudimentary, but I only had a few minutes to type it up. It should point you in the right direction.

SET #Product_Comments.Comments = COALESCE((
    SELECT STRING_AGG(p.Label, ', ')
    FROM (
        SELECT distinct Label, Product_ID
        FROM #TEMPORARYTB
        WHERE Product_ID = pc.Product_id
        ) as p  
    GROUP BY p.Product_ID
    ),'') + ' ' + COALESCE(pc.Comments,’’)
FROM #Product_Comments pc
JOIN Product_Property p ON p.Product_ID = pc.Product_id

Of course, I'd also have ask if you really need to concatenate comment back to itself in the first place?

1
  • Or you could simply remove the GROUP BY p.Product_ID from the original query. That way the subquery would always return a row, and COALESCE would do its substitution if the returned aggregated result happened to be null.
    – Andriy M
    Commented Feb 24, 2022 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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