Updating from a subquery when no results are returned-CodePudding

I have a post table that stores the most liked comment (top_comment).

I need an SQL statement that updates the top_comment when a comment is liked/unliked:

UPDATE post
SET top_comment = T1.body
FROM
(
    SELECT body
    FROM comment
    WHERE count_like > 0
    AND fk_post = 1
    ORDER BY count_like DESC
    LIMIT 1
) T1
WHERE pk_post = 1;

This works when there is a comment with likes however when no comments have likes I wish to update the top_comment to be NULL.

I'd prefer to do this as a single statement rather than two statements.

CodePudding user response：

try this:

UPDATE post
SET top_comment = T1.body
FROM
(
    (SELECT body
    FROM comment
    WHERE count_like > 0
    AND fk_post = 1
    ORDER BY count_like DESC
    LIMIT 1)

    UNION ALL --add these 2 lines
    SELECT NULL

) T1
WHERE pk_post = 1;

CodePudding user response：

EDITED: Added new method to do this with one statement

BELOW WE UPDATE WITH JOIN TO ACHEIVE THE SOLUTION IN SINGLE STATEMENT

UPDATE post 
SET top_comment = T1.body
From post p
Left join  (Select top(1) * from comment WHERE fk_post = 1 Order By count_like Desc)  T1 on pk_post=fk_post and count_like > 0
WHERE pk_post = 1;

These are two suggestion you can do Here you first your top comment value to NULL and then update it with new value if subquery returns any record.

UPDATE post
SET top_comment=NULL where pk_post=1

UPDATE post
SET top_comment = T1.body
FROM
(

    SELECT top 1 body
    FROM comment
    WHERE count_like > 0
    AND fk_post=1
    ORDER BY count_like DESC
  
) T1
where pk_post=1

**In the second suggestion you can check with IF EXIST **

DECLARE @setValue varchar(1000) = NULL
if Exists (SELECT top 1 body
    FROM comment
    WHERE count_like > 0
    AND fk_post=1
    ORDER BY count_like DESC)--SELECT top 1 body FROM comment)
Begin
  SELECT top 1  @setValue=body
    FROM comment
    WHERE count_like > 0
    AND fk_post=1
    ORDER BY count_like DESC
End
select @setValue

UPDATE post
SET top_comment = @setValue
where pk_post=1