MySQL Insert INTO using SELECT from another table with additional data

Question

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'.

INSERT INTO tableA (field1, field2, field3) (SELECT (field1, field2, 'somevalue') FROM tableB WHERE id='1'); — fortune
– fortune, Commented Dec 23, 2014 at 12:17

Gordon Linoff · Accepted Answer · 2014-12-23 12:20:17Z

14

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.

answered Dec 23, 2014 at 12:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Luis H Cabrejo Over a year ago

Great, worked perfectly simple

Dhaval · Accepted Answer · 2014-12-23 12:24:10Z

-1

Number of Insert statement columns should match with select columns when you insert data from another table In Your case this thing possible

INSERT INTO tableA (field1, field2, field3) 
SELECT (field1, field2,Null or 'Somevalue') FROM tableB WHERE id='1' // You have to pass some value or null or ''

edited Dec 23, 2014 at 12:24

answered Dec 23, 2014 at 12:08

Dhaval

2,3791 gold badge15 silver badges16 bronze badges

5 Comments

Amo Over a year ago

Perhaps my question wasn't clear. I don't want field3 to be a NULL or empty value. I need to set it, but outside of the scope of the INSERT.

Dhaval Over a year ago

can you please tell me what somevalue should be in field3?

Amo Over a year ago

Exactly that, 'somevalue'. It doesn't matter what it is, but it just isn't a value that is held or returned from tableB.

Dhaval Over a year ago

kk done one thing pass null right now than update null with you somevalue whenever you want..@BigPun

Amo Over a year ago

That would result in multiple queries which is exactly what this sort of sub querying is intended to avoid. See the marked answer.

Collectives™ on Stack Overflow

MySQL Insert INTO using SELECT from another table with additional data

What I'm Trying To Achieve

Example Problem

The Query So Far

The Issue

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I'm Trying To Achieve

Example Problem

The Query So Far

The Issue

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related