MySQL: Adding Calculated Column with IF ELSEIF

user_13968980 0
Viewed 610

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
)
;
calculated-columns ddl mysql
1 Answers

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

user_58413060
Related