My site has users with user_id and Customer_id. Default membership status is pending.
I want a query to change membership status to active by checking details in the other 2 tables. If users have bought a membership plan, change their status to active.
step 1- I want to select all users with pending status from the “membership” table.
table name: membership +---------+---------+ | user_id | status | +---------+---------+ | 1 | pending | | 2 | active | | 3 | pending | | 4 | pending | | 5 | active | +---------+--------+
step 2- select customer_id of users from step 1.
table name: Customers +---------+--------------+ | user_id | Customer_id | +---------+--------------+ | 1 | 17 | | 2 | 18 | | 3 | 21 | | 4 | 25 | | 5 | 29 | +---------+--------------+
step 3- check if the selected customers from step 2 have ordered product_id 92.
if yes then change status from pending to active in the “membership” table.
table name: orders +-------------+------------+ | Customer_id | Product_id | +-------------+------------+ | 21 | 85 | | 25 | 92 | | 29 | 99 | +-------------+------------+
End Result must look like
table name: membership +---------+---------+ | user_id | status | +---------+---------+ | 1 | pending | | 2 | active | | 3 | pending | | 4 | active | | 5 | active | +---------+--------+
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You can use update with join syntax:
update membership m
join Customers c on m.user_id = c.user_id
join orders o on c.Customer_id = o.Customer_id and o.Product_id = 92
set m.status = 'active'
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0