Question 22076 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Agents table

Booked slots table

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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`;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search