How to use Update in Case statement and column as alias

Question

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;

Answer 1

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;