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

How to use Update in Case statement and column as alias – Mysql

I have a table named sales_data where I have 3 columns (id int, udf varchar(20), date_of_sale datetime).
I’m trying to find weekday for the column date_of_sale by adjusting the time to 6 hours, now I have to update the column of udf as weekday corresponding to the date_of_sale. I have an idea of select query but how to update udf column?

select weekday(subtime(s.date_of_sale ,'6:0:0')) as putdata, 
CASE   
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=0 THEN 'Sunday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=1 THEN 'Monday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=2 THEN 'Tuesday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=3 THEN 'Wednesday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=4 THEN 'Thursday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=5 THEN 'Friday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=6 THEN 'Saturday'  
END as udf
from sales_data s;
Tags:

2

Answers


  1. 0

    I have an idea of select query but how to update udf column?

    Your above query is almost there. You just need to add update statement.

    The below query shall work for you.

    update sales_data 
set udf = case   
    when weekday(subtime(date_of_sale,'6:0:0')) = 0 then 'Sunday'  
    when weekday(subtime(date_of_sale,'6:0:0')) = 1 then 'Monday'  
    when weekday(subtime(date_of_sale,'6:0:0')) = 2 then 'Tuesday'  
    when weekday(subtime(date_of_sale,'6:0:0')) = 3 then 'Wednesday'  
    when weekday(subtime(date_of_sale,'6:0:0')) = 4 then 'Thursday'  
    when weekday(subtime(date_of_sale,'6:0:0')) = 5 then 'Friday'  
    when weekday(subtime(date_of_sale,'6:0:0')) = 6 then 'Saturday'  
 end;
    Login or Signup to reply.
  2. 0

    Add a generated column (https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html) to simplify handling, and also avoid data inconsistency:

    alter table sales_data add weekday varchar(10) GENERATED ALWAYS
  AS (CASE   
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=0 THEN 'Sunday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=1 THEN 'Monday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=2 THEN 'Tuesday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=3 THEN 'Wednesday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=4 THEN 'Thursday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=5 THEN 'Friday'  
    WHEN weekday(subtime(s.date_of_sale ,'6:0:0'))=6 THEN 'Saturday'  
END );

    See demo at: https://dbfiddle.uk/2d5iIvBv

    (I don’t get same weekdays, perhaps a regional setting?)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search