How to use Update in Case statement with columns as aliases-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

How to use Update in Case statement with columns as aliases

P粉214089349 2023-09-05 17:54:59

445

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?

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;

P粉214089349

reply all (2)

P粉2564870772023-09-06 14:26:54 2 floor

Addgenerated 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?)

Like+0

Add Reply

P粉0356005552023-09-06 14:04:15 1 floor

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;

Like+0

Add Reply