mysql - 使用 CASE 更新 VIRTUAL GENERATED 列

Question

我们更改了 KPI 的计算方式，但我无法更新表，但是当我将 UPDATE SET 与 CASE 一起使用时，它会失败。

UPDATE casedetails SET targetCompleted AS (
CASE
WHEN paidDate IS NULL THEN NULL
WHEN completionTargetType = 'A' THEN DATE_ADD(paidDate, INTERVAL 4 WEEK)
WHEN completionTargetType = 'B' THEN DATE_ADD(DATE_ADD(paidDate, INTERVAL 2 WEEK), INTERVAL 6 MONTH)
WHEN completionTargetType = 'C' THEN DATE_ADD(DATE_ADD(paidDate, INTERVAL 2 WEEK), INTERVAL 1 MONTH)
ELSE NULL
END);

我得到的错误是：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS (CASE WHEN paidDate IS NULL THEN NULL WHEN completionTargetType = 'A' THEN D' at line 1

表的SHOW CREATE如下：

| casedetails | CREATE TABLE `casedetails` (
  `delegateReference` varchar(20) NOT NULL,
  `renewalDate` date NOT NULL,
  `fatalDate` date DEFAULT NULL,
  `renewalStatus` varchar(100) NOT NULL,
  `iprType` varchar(20) NOT NULL,
  `territory` varchar(4) NOT NULL,
  `forms` varchar(100) DEFAULT NULL,
  `customer` varchar(100) NOT NULL,
  `agent` varchar(100) DEFAULT NULL,
  `dateInstructed` date NOT NULL,
  `dateSentToDV` date DEFAULT NULL,
  `dateDataChecked` date DEFAULT NULL,
  `dateDataDoubleChecked` date DEFAULT NULL,
  `dateSupplierAssigned` date DEFAULT NULL,
  `dateSentToSupplier` date DEFAULT NULL,
  `dateInHand` date DEFAULT NULL,
  `datePaid` date DEFAULT NULL,
  `dateCompleted` date DEFAULT NULL,
  `dateClosed` date DEFAULT NULL,
  `dateCancelled` date DEFAULT NULL,
  `whoSentToDV` varchar(5) DEFAULT NULL,
  `whoDataChecked` varchar(5) DEFAULT NULL,
  `whoDataDoubleChecked` varchar(5) DEFAULT NULL,
  `whoSupplierAssigned` varchar(5) DEFAULT NULL,
  `whoSentToSupplier` varchar(5) DEFAULT NULL,
  `whoInHand` varchar(5) DEFAULT NULL,
  `whoPaid` varchar(5) DEFAULT NULL,
  `whoCompleted` varchar(5) DEFAULT NULL,
  `whoClosed` varchar(5) DEFAULT NULL,
  `formType` varchar(20) DEFAULT NULL,
  `whoCancelled` varchar(5) DEFAULT NULL,
  `cancellationReason` varchar(50) DEFAULT NULL,
  `recordalStatus` varchar(20) DEFAULT NULL,
  `graceStatus` varchar(20) GENERATED ALWAYS AS ((case when (`dateInstructed` > `renewalDate`) then _cp850'Grace period' else _cp850'On time' end)) VIRTUAL,
  `processingWindow` int(11) GENERATED ALWAYS AS ((case when (`graceStatus` = _utf8mb4'On time') then (to_days(`renewalDate`) - to_days(`dateInstructed`)) when (`graceStatus` = _utf8mb4'Grace period') then (to_days(`fatalDate`) - to_days(`dateInstructed`)) else _cp850'Unknown' end)) VIRTUAL,
  `urgency` varchar(20) GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`processingWindow` >= 21)) then _cp850'Standard' when ((`iprType` = _utf8mb4'Trademark') and (`processingWindow` >= 45)) then _cp850'Standard' when (`processingWindow` >= 5) then _cp850'Expediated' when (`processingWindow` < 5) then _cp850'Urgent' else _cp850'Unknown' end)) VIRTUAL,
  `payThrough` varchar(20) GENERATED ALWAYS AS ((case when (`agent` like _utf8mb4'%Direct Payment') then _cp850'Pay Direct' when (`agent` like _utf8mb4'Envoy Supply Limited') then _cp850'Pay Direct' when isnull(`agent`) then NULL else _cp850'Agent' end)) VIRTUAL,
  `recordalType` varchar(20) GENERATED ALWAYS AS ((case when (`recordalStatus` = _utf8mb4'Requested') then _cp850'Recordal' when (`recordalStatus` = _utf8mb4'Outstanding') then _cp850'Recordal' when (`recordalStatus` = _utf8mb4'Completed') then _cp850'Recordal' when (`recordalStatus` = _utf8mb4'N/A') then _cp850'No recordal' else _cp850'Unknown' end)) VIRTUAL,
  `targetDataCheck` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 21 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 21 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard')) then (`dateInstructed` + interval 2 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Expediated')) then (`dateInstructed` + interval 2 day) else NULL end)) VIRTUAL,
  `targetDataDoubleCheck` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 20 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 20 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 45 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 45 day) else NULL end)) VIRTUAL,
  `targetSupplierAssigned` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 18 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 18 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 45 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 45 day) else NULL end)) VIRTUAL,
  `targetSentToSupplier` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 10 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 10 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 18 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 18 day) else NULL end)) VIRTUAL,
  `targetInHand` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Agent') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 4 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Agent') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 4 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Agent') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 14 day) when ((`iprType` = _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Agent') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 14 day) else NULL end)) VIRTUAL,
  `targetPaid` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 4 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 4 day) when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` <> _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'On time')) then `renewalDate` when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` <> _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'Grace period')) then `fatalDate` else NULL end)) VIRTUAL,
  `targetCompleted` date GENERATED ALWAYS AS ((case when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` <> _utf8mb4'Overdue')) then `renewalDate` when ((`iprType` <> _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Overdue')) then `fatalDate` else NULL end)) VIRTUAL,
  `targetClosed` date GENERATED ALWAYS AS (`fatalDate`) VIRTUAL,
  `kpiDataChecked` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateDataChecked`) and (`targetDataCheck` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateDataChecked`) and (`targetDataCheck` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (`targetDataCheck` < `dateDataChecked`) then 'Outside KPI' when (`targetDataCheck` > `dateDataChecked`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiDataDoubleChecked` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateDataDoubleChecked`) and (`targetDataDoubleCheck` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateDataDoubleChecked`) and (`targetDataDoubleCheck` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateDataDoubleChecked`) and (`targetDataDoubleCheck` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (`targetDataDoubleCheck` < `dateDataDoubleChecked`) then 'Outside KPI' when (`targetDataDoubleCheck` > `dateDataDoubleChecked`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiSupplierAssigned` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateSupplierAssigned`) and (`targetSupplierAssigned` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateSupplierAssigned`) and (`targetSupplierAssigned` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateSupplierAssigned`) and (`targetSupplierAssigned` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (isnull(`dateSupplierAssigned`) and (`targetSupplierAssigned` is not null) and (`renewalStatus` = 'Data Double Checked')) then 'Outstanding' when (`targetSupplierAssigned` < `dateSupplierAssigned`) then 'Outside KPI' when (`targetSupplierAssigned` > `dateSupplierAssigned`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiSentToSupplier` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateSentToSupplier`) and (`targetSentToSupplier` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateSentToSupplier`) and (`targetSentToSupplier` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateSentToSupplier`) and (`targetSentToSupplier` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (isnull(`dateSentToSupplier`) and (`targetSentToSupplier` is not null) and (`renewalStatus` = 'Data Double Checked')) then 'Outstanding' when (isnull(`dateSentToSupplier`) and (`targetSentToSupplier` is not null) and (`renewalStatus` like '%Assigned')) then 'Outstanding' when (`targetSentToSupplier` < `dateSentToSupplier`) then 'Outside KPI' when (`targetSentToSupplier` > `dateSentToSupplier`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiInHand` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateInHand`) and (`targetInHand` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateInHand`) and (`targetInHand` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateInHand`) and (`targetInHand` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (isnull(`dateInHand`) and (`targetInHand` is not null) and (`renewalStatus` = 'Data Double Checked')) then 'Outstanding' when (isnull(`dateInHand`) and (`targetInHand` is not null) and (`renewalStatus` like '%Assigned')) then 'Outstanding' when (isnull(`dateInHand`) and (`targetInHand` is not null) and (`renewalStatus` like 'Sent to%')) then 'Outstanding' when (`targetInHand` < `dateInHand`) then 'Outside KPI' when (`targetInHand` > `dateInHand`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiPaid` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Data Double Checked')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` like '%Assigned')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` like 'Sent to%')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Payment in Hand')) then 'Outstanding' when (`targetPaid` < `datePaid`) then 'Outside KPI' when (`targetPaid` > `datePaid`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiCompleted` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Data Double Checked')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` like '%Assigned')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` like 'Sent to%')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Payment in Hand')) then 'Outstanding' when (isnull(`dateCompleted`) and (`targetCompleted` is not null) and (`renewalStatus` = 'Paid')) then 'Outstanding' when (`targetCompleted` < `dateCompleted`) then 'Outside KPI' when (`targetCompleted` > `dateCompleted`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `kpiClosed` varchar(20) GENERATED ALWAYS AS ((case when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Holding')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Ready')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Data Checked')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Data Double Checked')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` like '%Assigned')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` like 'Sent to%')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Payment in Hand')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Paid')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Completed')) then 'Outstanding' when (isnull(`dateClosed`) and (`targetClosed` is not null) and (`renewalStatus` = 'Closed')) then 'Outstanding' when (`targetClosed` < `dateClosed`) then 'Outside KPI' when (`targetClosed` > `dateClosed`) then 'Inside KPI' else NULL end)) VIRTUAL,
  `onTimeDataChecked` int(11) GENERATED ALWAYS AS ((case when (`kpiDataChecked` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimeDataDoubleChecked` int(11) GENERATED ALWAYS AS ((case when (`kpiDataDoubleChecked` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimeSupplierAssigned` int(11) GENERATED ALWAYS AS ((case when (`kpiSupplierAssigned` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimeSentToSupplier` int(11) GENERATED ALWAYS AS ((case when (`kpiSentToSupplier` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimeInHand` int(11) GENERATED ALWAYS AS ((case when (`kpiInHand` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimePaid` int(11) GENERATED ALWAYS AS ((case when (`kpiPaid` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimeCompleted` int(11) GENERATED ALWAYS AS ((case when (`kpiCompleted` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `onTimeClosed` int(11) GENERATED ALWAYS AS ((case when (`kpiClosed` = 'Inside KPI') then 1 else 0 end)) VIRTUAL,
  `dbtDataChecked` int(11) GENERATED ALWAYS AS ((to_days(`targetDataCheck`) - to_days(`dateDataChecked`))) VIRTUAL,
  `dbtDataDoubleChecked` int(11) GENERATED ALWAYS AS ((to_days(`targetDataDoubleCheck`) - to_days(`dateDataDoubleChecked`))) VIRTUAL,
  `dbtSupplierAssigned` int(11) GENERATED ALWAYS AS ((to_days(`targetSupplierAssigned`) - to_days(`dateSupplierAssigned`))) VIRTUAL,
  `dbtSentToSupplier` int(11) GENERATED ALWAYS AS ((to_days(`targetSentToSupplier`) - to_days(`dateSentToSupplier`))) VIRTUAL,
  `dbtInHand` int(11) GENERATED ALWAYS AS ((to_days(`targetInHand`) - to_days(`dateInHand`))) VIRTUAL,
  `dbtPaid` int(11) GENERATED ALWAYS AS ((to_days(`targetPaid`) - to_days(`datePaid`))) VIRTUAL,
  `dbtCompleted` int(11) GENERATED ALWAYS AS ((to_days(`targetCompleted`) - to_days(`dateCompleted`))) VIRTUAL,
  `dbtClosed` int(11) GENERATED ALWAYS AS ((to_days(`targetClosed`) - to_days(`dateClosed`))) VIRTUAL,
  `ipanType` varchar(20) GENERATED ALWAYS AS ((case when (`agent` = 'Ipan GmbH') then 'ipan' when (`payThrough` = 'Pay Direct') then 'Pay Direct' else 'Not ipan' end)) VIRTUAL,
  `dvType` varchar(20) GENERATED ALWAYS AS ((case when ((`dateSentToDV` is not null) and (`whoDataChecked` = 'ADV')) then 'Auto DV' when ((`dateSentToDV` is not null) and (`whoDataChecked` <> 'ADV')) then 'Manual DV' when isnull(`dateSentToDV`) then 'Not DV' else NULL end)) VIRTUAL,
  `agentefficiency` int(11) GENERATED ALWAYS AS ((to_days(`dateInHand`) - to_days(`dateSentToSupplier`))) VIRTUAL,
  `paymentStatus` varchar(11) GENERATED ALWAYS AS ((case when (`renewalStatus` = 'Holding') then 'Unpaid' when (`renewalStatus` = 'Ready') then 'Unpaid' when (`renewalStatus` = 'Data Checked') then 'Unpaid' when (`renewalStatus` = 'Data Double Checked') then 'Unpaid' when (`renewalStatus` = 'Sent to DV') then 'Unpaid' when (`renewalStatus` like '%Assigned') then 'Unpaid' when (`renewalStatus` like 'Sent to%') then 'Unpaid' when (`renewalStatus` = 'Payment in Hand') then 'Unpaid' when (`renewalStatus` = 'Paid') then 'Paid' when (`renewalStatus` = 'Completed') then 'Paid' when (`renewalStatus` = 'Closed') then 'Paid' else NULL end)) VIRTUAL,
  `byDeadline` varchar(10) GENERATED ALWAYS AS ((case when ((`graceStatus` = 'On time') and (`datePaid` <= `renewalDate`)) then 'On time' when ((`graceStatus` = 'On time') and (`datePaid` > `renewalDate`)) then 'Late' when ((`graceStatus` = 'Grace period') and (`datePaid` <= `fatalDate`)) then 'On time' when ((`graceStatus` = 'Grace period') and (`datePaid` > `renewalDate`)) then 'Late' end)) VIRTUAL,
  `onTimeDeadline` int(11) GENERATED ALWAYS AS ((case when (`byDeadline` = 'On time') then 1 else 0 end)) VIRTUAL,
  `lateDeadline` int(11) GENERATED ALWAYS AS ((case when (`byDeadline` = 'Late') then 1 else 0 end)) VIRTUAL,
  `daysToClose` int(11) GENERATED ALWAYS AS ((to_days(`dateClosed`) - to_days(`dateInstructed`))) VIRTUAL,
  `daysToComplete` int(11) GENERATED ALWAYS AS ((to_days(`dateCompleted`) - to_days(`dateInstructed`))) VIRTUAL,
  `wasCancelled` int(11) GENERATED ALWAYS AS ((case when (`dateCancelled` is not null) then 1 else 0 end)) VIRTUAL,
  `paymentEfficiency` int(11) GENERATED ALWAYS AS ((to_days(`datePaid`) - to_days(`dateInHand`))) VIRTUAL,
  `lateDataChecked` int(11) GENERATED ALWAYS AS ((case when (`kpiDataChecked` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `lateDataDoubleChecked` int(11) GENERATED ALWAYS AS ((case when (`kpiDataDoubleChecked` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `lateSupplierAssigned` int(11) GENERATED ALWAYS AS ((case when (`kpiSupplierAssigned` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `lateSentToSupplier` int(11) GENERATED ALWAYS AS ((case when (`kpiSentToSupplier` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `lateInHand` int(11) GENERATED ALWAYS AS ((case when (`kpiInHand` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `latePaid` int(11) GENERATED ALWAYS AS ((case when (`kpiPaid` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `lateCompleted` int(11) GENERATED ALWAYS AS ((case when (`kpiCompleted` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `lateClosed` int(11) GENERATED ALWAYS AS ((case when (`kpiClosed` like 'Out%') then 1 else 0 end)) VIRTUAL,
  `completionTargetType` varchar(1) GENERATED ALWAYS AS ((case when (`payThrough` = 'Pay Direct') then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'AT')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'AU')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'BX')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'DK')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'EP')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'EUTM')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'FI')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'DE')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'HK')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'IE')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'NZ')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'NO')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'SG')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'SE')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'CH')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'GB')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'US')) then 'A' when ((`iprType` = 'Trademark') and (`territory` = 'WO')) then 'A' when (`ipanType` = 'ipan') then 'B' else 'C' end)) VIRTUAL,
  PRIMARY KEY (`delegateReference`)

我想知道我最好的行动方案是否只是原子删除并重新创建列，但由于其他虚拟列依赖于它，原子删除一大堆将是一件非常痛苦的事情。

标签： mysql