MySQL INSERT Query

Question

I need a query to perform the following: find the countryID where country = 'UK' from table Countries

then use the found value in

INSERT into towns (id, country_fk, name) values (1, <value_found>, 'London').

is this possible?

UPDATE I need to store the value once so that I can use it in multiple INSERTS (about 100)

Have you considered using an auto-incremented field for the id of the town? — Mark Byers
– Mark Byers, Commented Mar 31, 2010 at 22:04

Daniel Vassallo · Accepted Answer · 2010-03-31 21:58:57Z

5

Yes it is possible. One option is to use the INSERT ... SELECT syntax, as follows:

INSERT INTO towns (id, country_fk, name) SELECT 1, countryID, 'London' FROM countries WHERE country = 'UK';

answered Mar 31, 2010 at 21:58

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

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

4 Comments

Daniel Vassallo Over a year ago

@vittore: We call it the "fastest gun in the west" problem: meta.stackexchange.com/questions/9731/…

Daniel Vassallo Over a year ago

@mouthpiec: Hey, what a surprise to meet a compatriot! :)

Daniel Vassallo Over a year ago

@stereofrog: ...but faster from the other "exactly the same" answer(s) :)

zerkms Over a year ago

indeed, INSERT ... SELECT is more nice here. +1

vittore · Accepted Answer · 2010-03-31 21:59:29Z

insert into towns(id, countrt_fk, name) select 1 , countrt_fk , 'London' from Countries where country = 'UK'

user187291 · Accepted Answer · 2010-03-31 22:01:19Z

1

You can use subquery there:

INSERT into towns (id, country_fk, name) values (1, (SELECT countryID from countries where country = 'UK'), 'London')

edited Mar 31, 2010 at 22:01

user187291

54.1k19 gold badges98 silver badges127 bronze badges

answered Mar 31, 2010 at 21:58

zerkms

256k73 gold badges447 silver badges551 bronze badges

3 Comments

mouthpiec Over a year ago

thanks zerkms, but I need to store the value once so that I can use it in multiple INSERTS (about 100)

Mark Byers Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where country = 'UK'), 'London')'

user187291 Over a year ago

@Mark: the answer was missing the 'from' clause - are you getting any errors now?

Collectives™ on Stack Overflow

MySQL INSERT Query

3 Answers 3

4 Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Related