CODEWITHSUNDEEP
mysql
Shardul Tripathi
Shardul Tripathi

Reputation: 31

MySQL - Adding new column with default value when condition is met

I have an existing requests table in which I want to add a new column request_type. I want to set the default value of this column to A when a certain column is not null else B. I wrote the following query which shows syntax error for MySQL version 5.7.9 -

alter table requests
add column request_type enum('A','B','C') collate utf8_unicode_ci not null default (case when userid is not null then 'A' else 'B' end)

Upvotes: 0

Views: 1512

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562931

MySQL does not support expressions for the DEFAULT.

But you can use MySQL 5.7's new feature for GENERATED columns: http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

If you're using an older version of MySQL, you'd have to use a trigger to keep the enum column updated according to userid.

Upvotes: 1

Related Questions