Update one table conditionally based on values of another

Question

Hi, I have 2 tables: user_badges, and badges table, the following is user_badges table data:

id | user_id | badge_id | points_earned | badges_awarded |
 1    2324        0             5               0

If the user meets the minimum required points, i.e. 5 from the badges table, SQL will update the above as:

id | user_id | badge_id | points_earned | badges_awarded |
 1    2324        1             5               1

If, in future, there is new point registered for the same user, user_table badges will add a new row as follows:

 id | user_id | badge_id | points_earned | badges_awarded |
  1    2324        1             5               1
  2    2324        0             7               0

---

The above issue has been solved

---

This is badges table:

badge_id | badge_name | required_points
   1           new          5
   2           adv          10

---

Below is what I need

---

The problems is I need a query to compare the user_badges table to the badges table, provided that the query must know if the badges have been awarded before or not.

I am using this for Zend applications, need a solution for this problem...

Answer 1

If I understand your question correctly, first you need to get the users for whom badges are not yet awarded before, then using correlated subquery you need to update table user_badges with corresponding highest valid badges_id from badges tables.

UPDATE user_badges a
       INNER JOIN(SELECT DISTINCT user_id, SUM(badges_awarded) AS total_badges_awarded
                  FROM user_badges
                  GROUP BY user_id
                 ) b
             ON a.user_id = b.user_id
                AND b.total_badges_awarded = 0
SET a.badge_id = (@var_badges_awarded_flag:= (IFNULL((
                             SELECT c.badge_id
                             FROM badges c
                             WHERE a.points_earned > c.required_points
                             ORDER BY c.required_points DESC
                             LIMIT 1
                            ), 0))),
    a.badges_awarded = a.badges_awarded + IF(@var_badges_awarded_flag > 0, 1, 0);