Question 114905 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Updating from a subquery when no results are returned

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.

Tags:

4

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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 set 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
    Login or Signup to reply.
  3. 0

    You may use correlated subquery as a source of new value:

    update post
  set top_comment = (
    select c.body
    from comment as c
    where c.fk_post = post.pk_post
    order by count_like desc nulls last, pk_comment asc
    limit 1
  )
  where pk_post = 1;

    See db<>fiddle

    Login or Signup to reply.
  4. 0

    You can use union all select ‘default value’ where not exists (select 1 from T1)
    Something like this:

    WITH t1 AS
(
         SELECT   comment_body
         FROM     comment
         WHERE    count_like > 0
         AND      fk_post = 1
         ORDER BY count_like DESC limit 1)
UPDATE post
SET    top_comment =
       (
              SELECT t1.comment_body
              FROM   t1
              UNION ALL
              SELECT NULL
              WHERE  NOT EXISTS
                     (   SELECT 1
                            FROM   t1) )
WHERE  id = 1;

    This is just to answer your question but it’s dirty why would you put the id yourself and call the update yourself, the best solution is to use triggers as in : https://stackoverflow.com/a/73538944/6150881

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search