Reputation: 4033

MySQL INSERT Query

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)

Upvotes: 3

Answers (3)

Reputation: 254926

You can use subquery there:

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

Upvotes: 1

Reputation: 17579

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

Upvotes: 1

Reputation: 344311

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

Upvotes: 5