Mysql: user defined variable in Insert statement

Question

I am trying to use following mysql code to insert on column whose name is decided on some value. It gives me error that "some_percent" unknown column 'some_percent' in 'field list'

set @SplitState = `some_percent`;
SET @ID = uuid();
INSERT INTO `rspca_donations`(`id`, `@SplitState`) values (@ID, '100');

Can someone please suggest correct way?

Is there a column defined in the rspca_donations table called some_percent — RiggsFolly
– RiggsFolly, Commented Mar 19, 2019 at 9:05
You cannot use variables to refer to column or table names. The closest thing you can do is construct the whole query in a variable, then use PREPARE stmt FROM @query (followed by EXECUTE stmt and DROP PREPARE stmt). — Amadan
– Amadan, Commented Mar 19, 2019 at 9:11

Tim Biegeleisen · Accepted Answer · 2019-03-19 09:10:08Z

1

You can't use a user variable to represent a column name in this way. One option here would be to use a prepared statement in MySQL:

SET @SplitState = 'some_percent';
SET @ID = UUID();
SET @sql = CONCAT('INSERT INTO rspca_donations (id, ', @SplitState, ') VALUES (?, 100)');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @ID;

answered Mar 19, 2019 at 9:10

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

azka Over a year ago

Tim Biegeleisen, Thanks. It works for two columns. But I have long list of fields to be inserted in my real code.

Tim Biegeleisen Over a year ago

That sounds like a good topic for another question, my answer is intended to answer what you actually asked above.

Collectives™ on Stack Overflow

Mysql: user defined variable in Insert statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related