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
)
;
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.
Answered by Barbaros Özhan on November 4, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP