TransWikia.com

MySQL: Adding Calculated Column with IF ELSEIF

Stack Overflow Asked by thomasnngooglemailcom on November 4, 2021

I’m struggling with an Alter Table scenario.
I have a table "example" with columns 1 to 3 existing. I want to add a column "destination" which than should contain either a value from column2 or from column3 depending on column1.
I tried the following code but it throws errors at the first line of the IF statement. Error is with the column1 reference, it is expecting an opening parenthesis but when I put the IF clause’ condition in parantheses, it is rejecting the THEN function.
MySQL 8.0
See code below. Who can help? Many thanks,
Thomas


ADD COLUMN
(
Destination VARCHAR(15) 
GENERATED ALWAYS AS 
    (
    IF column1 = "north" THEN
        SET destination = column2
    ELSEIF column1 = "south" THEN   
        SET destination = column3
    ELSE
        SET destination = ""
    END IF
    )
STORED
)
;

One Answer

You can use the following syntax( containing CASE..WHEN..ELSE Statement instead of IF..THEN..ELSE ) of Generated Column which's released in DB version 5.7+ :

ALTER TABLE t1 ADD COLUMN
(
Destination VARCHAR(15) 
GENERATED ALWAYS AS 
    (
    CASE WHEN column1 = "north" THEN
           column2
         WHEN column1 = "south" THEN   
           column3    
    END 
    )
STORED
)

without assignment to the derived column itself and without set clause.

Demo

Answered by Barbaros Özhan on November 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP