So verwenden Sie die Anweisung „Update in Case' mit Spalten als Aliase

Question

Ich habe eine Tabelle namens sales_data mit 3 Spalten (id int, udf varchar(20), date_of_sale datetime). Ich versuche, den Wochentag der Spalte „date_of_sale“ zu finden, indem ich die Zeit auf 6 Stunden einstelle. Jetzt muss ich die udf-Spalte auf den Wochentag aktualisieren, der date_of_sale entspricht. Ich habe eine Idee für eine Auswahlabfrage, aber wie aktualisiere ich die UDF-Spalte?

selectweekday(subtime(s.date_of_sale ,'6:0:0')) as putdata,
FALL
    WHEN Weekday(subtime(s.date_of_sale ,'6:0:0'))=0 THEN 'Sonntag'
    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'
ENDE als udf
from sales_data s;

Answer 1

添加生成列（https://dev.mysql.com/doc/refman/8.0/en/create-table- generated-columns.html）以简化处理，并避免数据不一致：

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 );

查看演示：https://dbfiddle.uk/2d5iIvBv

（我没有得到相同的工作日，也许是区域设置？）

Answer 2

您的上述查询已经差不多了。您只需要添加更新语句即可。

下面的查询应该适合您。

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;