SELECT * FROM members m LEFT JOIN class_has_members cm ON(cm.member_id=m.member_id) WHERE cm.active NOT IN(0,6) AND cm.plan_status > 1

--Select query of people having the issue where their membership has been changed while on their class:

select mpa.allocation_id, mpa.class_member_id, mpa.membership_id as OLD_Membership_ID, mpa.member_membership_id as OLD_Member_membership_id, mhm.membership_id as NEW_Membership_ID, mhm.member_membership_id as NEW_Member_membership_ID, mhm.state, mhm.member_id
from member_payment_allocations mpa
inner join class_has_members chm
on mpa.class_member_id= chm.class_member_id
inner join member_has_memberships mhm
on chm.member_id = mhm.member_id
inner join payment_plans pp
on chm.plan_id = pp.plan_id
inner join payment_plan_option_has_memberships ppohm
on chm.option_id = ppohm.option_id and ppohm.membership_id = mhm.membership_id
where mpa.class_member_id in (SELECT cm.class_member_id FROM members m LEFT JOIN class_has_members cm ON(cm.member_id=m.member_id) WHERE cm.active NOT IN(0,6) AND cm.plan_status > 1)
AND chm.active = 1
AND mhm.state = 1
AND mpa.void_date is NULL
--and mhm.member_membership_id = mpa.member_membership_id
AND mpa.membership_id <> 0


--Insert into a temporary table:

SELECT mpa.allocation_id, mpa.class_member_id, mpa.membership_id as OLD_Membership_ID, mpa.member_membership_id as OLD_Member_membership_id, mhm.membership_id as NEW_Membership_ID, mhm.member_membership_id as NEW_Member_membership_ID, mhm.state, mhm.member_id
INTO #NewMemberships
FROM member_payment_allocations mpa
inner join class_has_members chm
on mpa.class_member_id= chm.class_member_id
inner join member_has_memberships mhm
on chm.member_id = mhm.member_id
where mpa.class_member_id in (SELECT cm.class_member_id FROM members m LEFT JOIN class_has_members cm ON(cm.member_id=m.member_id) WHERE cm.active NOT IN(0,6) AND cm.plan_status > 1)
AND chm.active = 1
AND mhm.state = 1
AND mpa.void_date is NULL
AND mpa.membership_id <> 0

--Perform the update:

begin tran
update mpa
set mpa.membership_id = new_membership_id, member_membership_id = new.new_member_membership_id
from member_payment_allocations mpa
INNER JOIN #newmemberships new
on mpa.allocation_id = new.allocation_id
rollback tran