0

Here's a question simple to solve in many other ways in the example I expose but in the real deal, my subqueries are more complex, but the principle to solve my own problem would be the same. A syntax problem most likely, so I reduced the problem to its simplest form.

Suppose I got the following tables:

/* contents_table */
  id │ field1   field3
 ════╪════════════════════ 
  1  │ os       devices_os


/* os_table */
  id │ devices_os  
 ════╪════════════ 
  1  │ android     
  2  │ ios         


/* my_table */
  id │ os       
 ════╪═════════ 
     │

contentsTable.field1 holds the name of the column I need to set in MyTable, contentsTable.field2 holds the name of the column in os_table where I need the values to be taken. If I do this the traditional way this is not a problem, as this:

INSERT INTO my_table (os)
SELECT 'devices_os'
FROM os_table

Will fill my_table as I need:

/* my_table */
  id │ os       
 ════╪═════════ 
  1  │ android  
  2  | ios

What I need to do is accomplish this same result but using subqueries. Using this same example, here are some erroneous attempts:

INSERT INTO my_table (SELECT field1 FROM contents_table)
SELECT (SELECT field2 FROM contents_table)
FROM os_table;


INSERT my_table (SELECT field1 FROM contents_table)
VALUES (SELECT field2 FROM contents_table)
FROM os_table;


INSERT INTO my_table SELECT CONCAT('(', '(SELECT field1 FROM contents_table)', ')')
SELECT (    SELECT CONCAT('`', '(SELECT field2 FROM contents_table)', '`')    )
FROM os_table;

Seems possible but I can't figure it out. Please enlighten me!

Metafaniel
  • 29,318
  • 8
  • 40
  • 67
  • Seems possible, but it isn't. You have to put actual column names there. You could of course build up a query based on the results of another query, but those are separate steps: Query the columns, build an SQL string for the insert statement, execute that statement. – GolezTrol Oct 14 '20 at 07:33
  • Depending on your environment there are different ways to do that (in MySQL, or in the client script/application), but [this answer](https://stackoverflow.com/a/50580965/511529) shows it in a MySQL stored proc. – GolezTrol Oct 14 '20 at 07:37
  • @GolezTrol That question/answer is unclear to me, and now with my question closed I don't understand what to do next....... – Metafaniel Oct 14 '20 at 14:15

0 Answers0