CODEWITHSUNDEEP
mysqlsql
Solace
Solace

Reputation: 9010

How to add a value from one table to a particular row in another table?

I need to use a command like this:

insert into PERSON (hometown)
where person_name="Peter"

select city from COUNTRY
where country_id= 2;

It is obviously wrong!

In a nutshell, a command which will select a particular value (country's city from the record where the id is 2) from one table and add it to another table (person's hometown whose name is Peter), so basically a particular attribute's value from one table to another table's particular row.

What will be the command for that?

Upvotes: 0

Views: 70

Answers (2)

Ian Laird
Ian Laird

Reputation: 442

Assuming mysql: you want to do something like this.

UPDATE Person, Country
SET Person.City=Country.City
WHERE Person.Person_Name = 'Peter'
AND City.Country_id = 2

http://dev.mysql.com/doc/refman/5.0/en/update.html

If you are doing insert specificly, i would recomend something like this:

insert into PERSON (hometown, person_name)
select city, 'Peter' from COUNTRY where country_id= 2;

Just make sure the ID is unique or you will end up with more then one record named 'peter'.

Upvotes: 1

Gregg_1987
Gregg_1987

Reputation: 443

This should work:

UPDATE Person SET City=(SELECT City FROM Country WHERE Country_id = 2) 
WHERE Person_Name = 'Peter'

Upvotes: 2

Related Questions