BEGIN;
--会員ポイント追加
UPDATE members SET mem_points = mem_points + (
		SELECT sum(course_points) FROM members_courses 
			LEFT JOIN courses USING(course_no) 
			WHERE members_courses.mem_no = members.mem_no
	) + (
		SELECT sum(course_monthly_bonus) FROM members_courses 
			LEFT JOIN courses USING(course_no) 
			WHERE members_courses.mem_no = members.mem_no
	)
	WHERE EXISTS (
		SELECT 1 FROM members_courses 
		WHERE members_courses.mem_no = members.mem_no
	)
	AND mem_mp = 1
	AND mem_flg_cd = 2;
--ログ
INSERT INTO official_monthly_charges(mem_no, med_no, course_no, mp, ins_ts, course_reward)
	SELECT mem_no, med_no, course_no, mem_mp as mp, CURRENT_TIMESTAMP as ins_ts, course_reward FROM members
		INNER JOIN members_courses USING(mem_no)
			WHERE mem_mp = 1
			AND mem_flg_cd = 2;
--最大持ち越しポイント
UPDATE members SET mem_points = 20000 WHERE mem_points > 20000;
COMMIT;
