UPDATE with multiple JOIN on CASE

Question

I am writing an UPDATE sql query within Postgres 12.

The query is very complex and it needs to contain JOIN and CASE in order to work. I can not get my head around it so far. I need to update an approval_status column on the comment_response table.

The team_member_manager table indicates who needs to approve comments and they are only valid ones (even if there are more others in the post_comment_response_approval)

There are 4 Cases I need:

• If everyone who needs to submit responses has submitted an approval, then the status - should be approved.

• If there are no associated team_member_manager records, the status also should be approved.

• If anyone has rejected it, the status should be rejected.

• Otherwise, the status should be pending.

Here are table structures with right values.

post_comment_response table:

id	post_comment_id	comment	approval_status
1	1173	Hello World	NULL

post_comment table:

id	post_id
1173	652

post table:

id	message_id	team_member_id
652	110	60735

team_member_manager table:

id	managing_team_member_id	managed_team_member_id
55	68893	60735
56	68893	60736

team_member table:

id	team_id	member_id
68893	91	1

post_comment_response_approval table:

id	post_comment_response_id	team_member_id	approved	note
54	1	60735	true	This one should be included
70	1	666	true	This should not
70	1	60736	false	This should be included

NOTE: I'am defining how is manager and who is managed member by doing JOIN managing_team_member_id is the one with defined value (91) managed_team_member_id value I want to get by JOINing post_comment → post

My sql statement and JOINs are working as it should but I got stuck on how to implement them properly with CASEs. Can someone please help?

UPDATE post_comment_response pcr
SET
approval_status = CASE WHEN 'first case'= 'approved'
                  WHEN 'second case' = 'approved',
                  WHEN 'third case' = 'rejected'
                  ELSE 'pending'
JOIN post_comment pc ON pc.id = 1173
JOIN post p ON p.id = pc.post_id
JOIN team_member_manager tmm ON tmm.managed_team_member_id = p.team_member_id
JOIN team_member mgr ON mgr.id = tmm.managing_team_member_id
WHERE mgr.team_id = 91;

Answer 1

"if all related rows from team_member_manager_approval are approved then approve, if even one of them is false, then reject"

To get this result, you need to use the aggregate function bool_and applied to the column approved of table post_comment_response_approval while grouping the rows on post_comment_response.id

"if there are no rows also approve"

To get this result, you need to LEFT JOIN the table post_comment_response_approval with other tables so that if no row matches, then a NULL value will be populated for the columns coming from that table.

Then you can use a CASE WHEN ... THEN ... END statement to convert the boolean result of bool_and into a text : true => 'approve', false => 'reject'. If no row exists in table post_comment_response_approval then bool_and(...) will return NULL and COALESCE(bool_and(...), true) will return true so that to select the 'approve' value.

As you can't use an aggregate function directly in the SET clause of an UPDATE, then you have to implement the query in a cte, and to refer to the cte result in the UPDATE.

Starting from your data sample, and trying to follow your business logic, the solution to update the approval_status in the post_comment_response table and which corresponds to post_comment = 1173 and team_member.team_id = 91 is :

WITH list AS (
SELECT 
       pcr.id, pcr.post_comment_id, pcr.comment
     , CASE WHEN COALESCE(bool_and(pcra.approved), true) THEN 'approve' WHEN NOT bool_and(pcra.approved) THEN 'reject' ELSE 'pending' END AS approval_status
  FROM post_comment_response pcr
  LEFT JOIN post_comment_response_approval pcra ON pcra.post_comment_response_id = pcr.id
  JOIN team_member_manager tmm ON tmm.managed_team_member_id = pcra.team_member_id
  JOIN team_member tm ON tm.id = tmm.managing_team_member_id
 WHERE pcr.post_comment_id = 1173
   AND tm.team_id = 91
  GROUP BY pcr.id, pcr.post_comment_id, pcr.comment
)
UPDATE post_comment_response pcr
   SET approval_status = l.approval_status
  FROM list l
 WHERE pcr.id = l.id ;

But you can also update all the rows of the post_comment_response table at once with the following query :

WITH list AS (
SELECT 
       pcr.id, pcr.post_comment_id, pcr.comment
     , CASE WHEN COALESCE(bool_and(pcra.approved), true) THEN 'approve' WHEN NOT bool_and(pcra.approved) THEN 'reject' ELSE 'pending' END AS approval_status
  FROM post_comment_response pcr
  LEFT JOIN post_comment_response_approval pcra ON pcra.post_comment_response_id = pcr.id
  JOIN team_member_manager tmm ON tmm.managed_team_member_id = pcra.team_member_id
  JOIN team_member tm ON tm.id = tmm.managing_team_member_id
  JOIN post_comment pc ON pc.id = pcr.post_comment_id
  JOIN post p ON p.id = pc.post_id
  JOIN team_member_manager tmm2 ON tmm2.managed_team_member_id = p.team_member_id AND tmm2.managing_team_member_id = tm.id
 GROUP BY pcr.id, pcr.post_comment_id, pcr.comment
)
UPDATE post_comment_response pcr
   SET approval_status = l.approval_status
  FROM list l
 WHERE pcr.id = l.id ;

see the test result in dbfiddle