通过id匹配条件，根据另一个更新一个MySQL表值

Question

I have these 2 tables below : users:

         id 7dexpn
=========== ==========
          1 0
          2 0
          3 0


user_pages:
        id     user_id 7dexpf
========== =========== ==========
       99           1 0
       98           2 1
       97           3 1
       96           3 1
       95           3 1
       94           2 0

I have successfully insert { user_pages aggregate of (7dexpf) flags} into users table (7dexpn) matching by user_pages (user_id) with users table (id) with this query

update users u join
       (select user_id, count(*) as cnt
        from user_pages 
        where `7dexpf` = 1
        group by user_id
       ) uu
       on uu.user_id = u.id
    set u.`7dexpn` = uu.cnt;

But the query not update flag back to zero if u.7dexpn = 0

Answer 1

Use a LEFT JOIN with COALESCE() :

UPDATE users u 
LEFT JOIN
     (SELECT user_id, COUNT(*) as cnt
      FROM user_pages 
      WHERE `7dexpf` = 1
      GROUP BY user_id
     ) uu
 ON uu.user_id = u.id
SET u.`7dexpn` = COALESCE(uu.cnt,0);