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