MySQL: Adding Calculated Column with IF ELSEIF

Question

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
)
;

Barbaros Özhan · 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

MySQL: Adding Calculated Column with IF ELSEIF

One Answer

Add your own answers!

Ask a Question