MariaDB INSERT value as COLUMN_CREATE (dynamic column)

Question

I need insert dynamic column as select from another table.

I tried:

INSERT INTO table (id, photos)
VALUES
(12345,COLUMN_CREATE(SELECT file FROM photo WHERE ad_id = 12345));

and

INSERT INTO table (id, photos)
VALUES
(12345,SELECT COLUMN_CREATE(file) FROM photo WHERE ad_id = 12345));

and

INSERT INTO table (id, photos)
SELECT ad_id, COLUMN_CREATE(file) FROM photo WHERE ad_id = 12345;

And MariaDB always returns #1064 - You have an error in your SQL syntax;

score 0 · Accepted Answer · answered Oct 22 '14 at 14:25

0

INSERT INTO `table` (id, photos)
VALUES
(12345,COLUMN_CREATE(1, (SELECT file FROM photo WHERE ad_id = 12345)));

Put quotes around table as it is a reserved word (List of MariaDB's reserved words).

Add the dynamic column's number or name to the COLUMN_CREATE function's arguments (COLUMN_CREATE syntax), my example SQL uses 1 as column number.

Put the SELECT statement in parentheses as subqueries must be parenthesized.

answered Oct 22 '14 at 14:25

Julian Ladisch

Thanks! But now MariaDB return **#1242 - Subquery returns more than 1 row** I need insert more values (rows) into dynamic column and I thought MariaDB automatically process it. – Ondřej Machala Oct 23 '14 at 08:37
@Ondřej: Neither COLUMN_CREATE nor COLUMN_ADD can automatically create multiple dynamic columns from a subquery with more than 1 row. – Julian Ladisch Oct 23 '14 at 10:52

1 Answers1