Update Column Value in one table based on count entries in another table-CodePudding

I have two tables named board_votes and req_form. When a form_id in the board_votes table is equal to 2 (i.e two board member votes means approved) I want to change the value of the column Board_approval in the req_form table to 'yes'.

Each Board_approval in the req_form table is set at 'pending' and only on atleast two form_id entries into the board_votes can the Board_approval value be changed to 'yes'.

board_votes table:

 --------- ------------ ---------- 
| vote_id |  form_id   | board_id |
 --------- ------------ ---------- 
|       1 |         22 |        1 |
|       3 |         22 |        3 |
|       4 |         26 |        1 |
|       5 |          7 |        1 |
|       6 |         19 |        1 |
 --------- ------------ ----------

req_form:

 ------------------------- 
| form_id  Board_approval |
 ------------------------- 
| 7          pending      |
| 19         pending      |
| 22         pending      |
| 23         pending      |
 -------------------------

Here's the sql query I'm using also:

"Update req_form 
 SET Board_approval = 'yes' 
 HAVING COUNT(board_votes.form_id = '22') >1";

CodePudding user response：

UPDATE rf
SET rf.BoardApproval = 'Yes'
FROM req_form rf
JOIN 
(
SELECT form_id FROM board_votes bv 
GROUP BY bv.form_id 
HAVING COUNT(bv.vote_id) >= 2
) TwoVotes ON TwoVotes.form_id = rf.form_id

CodePudding user response：

The task can be decomposed into two steps. First, you want two get all form_ids for which more than 1 vote exists:

SELECT form_id 
  FROM board_votes
 GROUP BY form_id
HAVING count(*) > 1

Second, you can update all approvals which form_id is in the result set of the query above:

UPDATE req_form SET Board_approval = 'yes'
 WHERE form_id IN (
    SELECT form_id 
      FROM board_votes
     GROUP BY form_id
    HAVING count(*) > 1)
-- AND optional additional conditions
    ;

Alternatively, you can derive a table from the query above and join it with table req_form. This can be found in the answers of planetmatt and Zaynul Abadin Tuhin, so I'll repeat it just for the sake of completeness:

UPDATE req_form INNER JOIN (
      SELECT form_id 
        FROM board_votes
       GROUP BY form_id
      HAVING count(*) > 1) forms_of_interest
    ON req_form.form_id = forms_of_interest.form_id
   SET req_form.Board_approval = 'yes'
-- optional WHERE clause
   ;