MySQL - str_to_date() 在更新查询中不起作用

Question

我正在尝试执行一些计算并使用 SQL 查询使用计算值更新表。使用的查询如下:

    UPDATE `table1` pv inner join 
(SELECT  Email,IF(`DS Start Date` = "-",Round((DATEDIFF(CURDATE(),STR_TO_DATE(`Start Date`,'%Y-%m-%d')))/365,2),
         Round((DATEDIFF(STR_TO_DATE(`DS Start Date`,'%Y-%m-%d'),STR_TO_DATE(`Start Date`,'%Y-%m-%d')))/365,2)) as ba_tenure,
    IF(`DS End Date` != "-",Round((DATEDIFF(STR_TO_DATE(`DS End Date`,'%Y-%m-%d'),STR_TO_DATE(`DS Start Date`,'%Y-%m-%d')))/365,2),
    Round((DATEDIFF(CURDATE(),STR_TO_DATE(`DS Start Date`,'%Y-%m-%d')))/365,2)) as ds_tenure,
    Round((DATEDIFF(CURDATE(),STR_TO_DATE(`Start Date`,'%Y-%m-%d')))/365,2) as overall_tenure
                      from `table1` where Status = "Active" ) A on (pv.Email = A.Email)      
                      set  pv.`Tenure (Yrs)` = A.overall_tenure,pv.`BA Team Tenure` = A.ba_tenure,pv.`DS Team Tenure` = A.ds_tenure

当我执行select语句时，查询运行良好。但是当我执行 update 查询时，它会抛出错误

#1411 - Incorrect datetime value: '-' for function str_to_date

我无法弄清楚这个问题。谁能帮我解决这个问题吗？

Answer 1

如果您的第二个条件是 DS 开始日期 为 true 和 false，但如果为 false，则表示 DS 开始日期 等于“-”，而该值不等于对于 str_to_date

有效

IF(`DS End Date` != "-",
Round((DATEDIFF(STR_TO_DATE(`DS End Date`,'%Y-%m-%d'),STR_TO_DATE(`DS Start Date`,'%Y-%m-%d')))/365,2),
Round((DATEDIFF(CURDATE(),STR_TO_DATE(`DS Start Date`,'%Y-%m-%d')))/365,2)) as ds_tenure,