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 | +---------+--------+
Advertisement
Answer
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'