MySQL Insert INTO using SELECT from another table with additional data

MySQL

What I'm Trying To Achieve

I'm trying to insert a record from one table into another, but also add additional data that isn't sourced from the second table.

Example Problem

I'm trying to put insert field1, field2 and field3 into tableA. field1 and field2 are sourced from tableB. However, field3 is some additional arbitrary data that I will populate using my application.

The Query So Far

INSERT INTO tableA (field1, field2, field3) SELECT (field1, field2) FROM tableB WHERE id='1'

The Issue

Field3 is not being inserted at the moment as the above query doesn't have the field in the query. I've tried:

INSERT INTO tableA (field1, field2, field3) (SELECT (field1, field2) FROM tableB WHERE id='1'), 'somevalue';

but that doesn't seem to work.

I don't want field3 to be set to NULL or an empty string. I need to populate that column, however I need it to be populated with data from outside of the scope of the INSERT. In the example above, it should be 'somevalue'.

Best Answer

You would just include the constant in the select list:

INSERT INTO tableA(field1, field2, field3)
    SELECT field1, field2, 'somevalue'
    FROM tableB
    WHERE id = '1';

Also, don't surround the columns in a select in parentheses.

Related Topic