How to use Update in Case statement with columns as aliases
P粉214089349
2023-09-05 17:54:59
<p>I have a table called sales_data with 3 columns (id int, udf varchar(20), date_of_sale datetime).
I am trying to find the weekday of date_of_sale column by adjusting the time to 6 hours, now I have to update the udf column to the weekday corresponding to date_of_sale. I have an idea for a select query, but how do I update the udf column? </p>
<pre class="brush:php;toolbar:false;">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;</pre></p>
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Add generated columns (https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html) to simplify processing , and avoid data inconsistencies:
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 );View the demo: https://dbfiddle.uk/2d5iIvBv
(I don't get the same weekdays, maybe it's the locale?)
Your above query is almost complete. You just need to add update statements.
The query below should 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;