Search code examples
mariadbdynamic-columnsselect-insert

MariaDB INSERT value as COLUMN_CREATE (dynamic column)

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;

Solution 
  • 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.