How to add column with default value from a variable in MySQL?

Question

I've tried

SET @branch := 'BRANCH_A';

ALTER TABLE item
ADD COLUMN branch VARCHAR(15) NOT NULL DEFAULT @branch FIRST;

This returns error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@branch FIRST' at line 2

This is working fine

ALTER TABLE item
ADD COLUMN branch VARCHAR(15) NOT NULL DEFAULT 'BRANCH_A' FIRST;

Where did I go wrong?

You didn't go wrong, You cannot default a column to variable - unless you use dynamic sql.Is there some reason you want to do something like this? — P.Salmon
– P.Salmon, Commented Jun 12, 2019 at 11:19
@P.Salmon I'm making a snippets because we need to export, alter, and reupload tables when things go wrong (this happens quite often because of bad internet connection in our client shop). There will be many line, not just ADD COLUMN branch, so I think that using variable at the top is tidier than replacing the value in the middle. It is just a temporary quick fix while we are working on a program to do this automagically. — Christhofer Natalius
– Christhofer Natalius, Commented Jun 12, 2019 at 11:25
not sure whats going wrong with a bad internet connection but still, maybe you can erborate a more by explaining the user case? it is a running (web) application which could store information? — Raymond Nijland
– Raymond Nijland, Commented Jun 12, 2019 at 11:31
if a webapplication is the case you could make the web application a offline application with html 5 cache and use WebSQL or IndexedDB to store the infromation local when the internet connection goes down.. HTML 5 has API's to detect that when the internet is back online push the information from the local database to the server.. But offcource the better option would be to get better internet if that possible over there. — Raymond Nijland
– Raymond Nijland, Commented Jun 12, 2019 at 11:33
@RaymondNijland The client has several branch running a local desktop program, and then the program upload the item stock to online server so that other branch can see item stock in each branch. But if the connection is bad, of course the items stock will become obsolete. And when the queue become too many, we need to upload it manually to server. — Christhofer Natalius
– Christhofer Natalius, Commented Jun 13, 2019 at 2:14

guyaloni · Accepted Answer · 2021-10-25 14:41:01Z

2

You need to use dynamic sql in order to use a variable in your query:

SET @branch := 'BRANCH_A';
SET @query := CONCAT('ALTER TABLE item ADD COLUMN branch VARCHAR(15) NOT NULL DEFAULT "', @branch, '" FIRST');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;

answered Oct 25, 2021 at 14:41

guyaloni

6,0206 gold badges63 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add column with default value from a variable in MySQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related