gistfile1.txt

SELECT * FROM memberships mm
   LEFT JOIN (
      SELECT m.thread, MAX(m.id) AS message FROM messages m
      WHERE m.type != 3
      GROUP BY m.thread 
    ) all_users_query ON mm.thread = all_users_query.thread
    LEFT JOIN (
      SELECT m.thread, stm.user, MAX(m.id) AS message FROM messages m
      LEFT JOIN memberships stm ON m.type = 3
        AND stm.thread = m.content
      WHERE JSON_EXTRACT(stm.permissions, '$.visible.value') IS TRUE
      GROUP BY m.thread, stm.user
    ) last_subthread_message_for_user_query 
    ON mm.thread = last_subthread_message_for_user_query.thread 
      AND mm.user = last_subthread_message_for_user_query.user
    WHERE (mm.user, mm.thread) IN (('3033752', '1'));

UPDATE memberships mm
   LEFT JOIN (
      SELECT m.thread, MAX(m.id) AS message FROM messages m
      WHERE m.type != 3
      GROUP BY m.thread 
    ) all_users_query ON mm.thread = all_users_query.thread
    LEFT JOIN (
      SELECT m.thread, stm.user, MAX(m.id) AS message FROM messages m
      LEFT JOIN memberships stm ON m.type = 3
        AND stm.thread = m.content
      WHERE JSON_EXTRACT(stm.permissions, '$.visible.value') IS TRUE
      GROUP BY m.thread, stm.user
    ) last_subthread_message_for_user_query 
    ON mm.thread = last_subthread_message_for_user_query.thread 
      AND mm.user = last_subthread_message_for_user_query.user
    SET
      mm.last_message = GREATEST(COALESCE(all_users_query.message, 0), 
        COALESCE(last_subthread_message_for_user_query.message, 0)),
      mm.last_read_message = GREATEST(COALESCE(all_users_query.message, 0), 
        COALESCE(last_subthread_message_for_user_query.message, 0))
    WHERE (mm.user, mm.thread) IN (('3033752', '1'));