How do I use the result from a SELECT statement in an ALTER COLUMN statement in MySQL

Asked Jul 30 '20 at 11:17

Active Jul 30 '20 at 11:30

Viewed 36 times

I want to use the id returned from a SELECT statement as the default value in an ALTER COLUMN statement.

The select statement

SELECT id from table_a WHERE name='DISCOUNT';

The alter statement

ALTER TABLE table_b
  ADD COLUMN column_a SMALLINT NOT NULL DEFAULT <id returned>

asked Jul 30 '20 at 11:17

Paul Tofunmi

'Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.' - https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html . I wonder why you thought this was allowed. Perhaps a trigger instead. – P.Salmon Jul 30 '20 at 11:43
You can use [dynamic sql](https://stackoverflow.com/q/190776) for that. – Solarflare Jul 30 '20 at 11:53
Please provide an example of dynamic sql – Paul Tofunmi Jul 31 '20 at 04:22

0 Answers0