Search code examples
mysqlenumssql-updatedateadd

Updating a value from a table that is dependent on the value of an ENUM column in another table

I have one table placed_order that contains the columns order_time (timestamp), estimated_delivery_time (timestamp) and more. In a second table addresses there is the column zone (apart from others). The zone column is an ENUM field and contains the values 'Zone 1', 'Zone 2', 'Zone 3'.

Now I need to calculate the estimated_delivery_time. Depending on the zones, it is the order_time plus 10mins (Zone 1), 15mins (Zone 2) ord 20mins (Zone 3).

I already found out how to calculate the estimated_delivery_time by manually adding the minutes:

UPDATE `placed_order` 
SET `estimated_delivery_time` = DATE_ADD(`order_time`, INTERVAL 10 MINUTE)
WHERE placed_order_id = 9;

But I'm absolutely struggling to find a solution how the estimated_delivery_time can be calculated depending on the zones. I already tried it with SELECT IF and CASE clauses but couldn't find a way. Any suggestions?

Solution

  • You can use Case .. When to update the estimated_delivery_time. Try the following query:

    UPDATE `placed_order` AS po 
JOIN `addresses` AS addr ON addr.customer_id = po.customer_id 
SET po.estimated_delivery_time = CASE addr.zone 
                                   WHEN 'Zone 1' 
                                     THEN DATE_ADD(po.order_time, INTERVAL 10 MINUTE)
                                   WHEN 'Zone 2' 
                                     THEN DATE_ADD(po.order_time, INTERVAL 15 MINUTE)
                                   WHEN 'Zone 3' 
                                     THEN DATE_ADD(po.order_time, INTERVAL 20 MINUTE)
                                 END