How to Update a column in mySQL conditionally based on results from another table-CodePudding

I have two related tables where I need to update and set a column based on count from the other table. I have an agents table where each agent has a level which is to be computed based on the number of booked slots. The system has the Agent details where each Agent has different levels of dealer namely bronze, silver and gold. The Agent’s level is determined based on the number of bookings, where if an Agent have, equal or more than, 10 bookings, it is categorised as Gold, more than 4, but less then, 10 as Silver, and Bronze otherwise. How can I update the level column to display this. Below are the agents table and booked slots table. Thanks in advance.

CodePudding user response：

It is more efficient to run the aggregation query once, than for each row -

UPDATE `agents` `a`
INNER JOIN (
    SELECT
        `agents_tbl_id`,
        CASE
            WHEN COUNT(*) >= 10 THEN 'Gold'
            WHEN COUNT(*) > 4 THEN 'Silver'
            ELSE 'Bronze'
        END `level`
    FROM `bookings`
    GROUP BY `agents_tbl_id`
) `b`
    ON `a`.`id` = `b`.`agents_tbl_id`
SET `a`.`level` = `b`.`level`;

CodePudding user response：

I think the following query should help you. Unfortunately I don't have the possibility to check it right now. But I hope the approach brings you further.

UPDATE customers 
SET customers.level = (SELECT IF(count(*) > 10, "Gold", IF(count(*) > 4, "SILVER", "Bronze"))
                       FROM bookings 
                       WHERE bookings.customer_tbl_id = customers.id)