title: Update VIRTUAL GENERATED column with CASE
body: <p>We've changed how our KPIs are calculated, but I can't update the table, but when I use UPDATE SET with CASE, it fails.</p>
  
  <pre class="lang-sql prettyprint-override"><code>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);
  </code></pre>
  
  <p>The error I get reads:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The SHOW CREATE of the table is as follows:</p>
  
  <pre><code>| 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` &gt; `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` &lt;&gt; _utf8mb4'Trademark') and (`processingWindow` &gt;= 21)) then _cp850'Standard' when ((`iprType` = _utf8mb4'Trademark') and (`processingWindow` &gt;= 45)) then _cp850'Standard' when (`processingWindow` &gt;= 5) then _cp850'Expediated' when (`processingWindow` &lt; 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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 21 day) when ((`iprType` &lt;&gt; _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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 20 day) when ((`iprType` &lt;&gt; _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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 18 day) when ((`iprType` &lt;&gt; _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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 10 day) when ((`iprType` &lt;&gt; _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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Agent') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 4 day) when ((`iprType` &lt;&gt; _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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'On time')) then (`renewalDate` - interval 4 day) when ((`iprType` &lt;&gt; _utf8mb4'Trademark') and (`urgency` = _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'Grace period')) then (`fatalDate` - interval 4 day) when ((`iprType` &lt;&gt; _utf8mb4'Trademark') and (`urgency` &lt;&gt; _utf8mb4'Standard') and (`payThrough` = _utf8mb4'Pay Direct') and (`graceStatus` = _utf8mb4'On time')) then `renewalDate` when ((`iprType` &lt;&gt; _utf8mb4'Trademark') and (`urgency` &lt;&gt; _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` &lt;&gt; _utf8mb4'Trademark') and (`urgency` &lt;&gt; _utf8mb4'Overdue')) then `renewalDate` when ((`iprType` &lt;&gt; _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` &lt; `dateDataChecked`) then 'Outside KPI' when (`targetDataCheck` &gt; `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` &lt; `dateDataDoubleChecked`) then 'Outside KPI' when (`targetDataDoubleCheck` &gt; `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` &lt; `dateSupplierAssigned`) then 'Outside KPI' when (`targetSupplierAssigned` &gt; `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` &lt; `dateSentToSupplier`) then 'Outside KPI' when (`targetSentToSupplier` &gt; `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` &lt; `dateInHand`) then 'Outside KPI' when (`targetInHand` &gt; `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` &lt; `datePaid`) then 'Outside KPI' when (`targetPaid` &gt; `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` &lt; `dateCompleted`) then 'Outside KPI' when (`targetCompleted` &gt; `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` &lt; `dateClosed`) then 'Outside KPI' when (`targetClosed` &gt; `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` &lt;&gt; '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` &lt;= `renewalDate`)) then 'On time' when ((`graceStatus` = 'On time') and (`datePaid` &gt; `renewalDate`)) then 'Late' when ((`graceStatus` = 'Grace period') and (`datePaid` &lt;= `fatalDate`)) then 'On time' when ((`graceStatus` = 'Grace period') and (`datePaid` &gt; `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`)
  </code></pre>
  
  <p>I am wondering if my best course of action is simply an atomic drop and re-create the column, but as other virtual columns are dependent on it, it would be a total pain to atomic drop a whole bunch.</p>