CODEWITHSUNDEEP
mysqlinsert
user609239
user609239

Reputation: 3366

Inserting data from one table into another

There is a one table,

City[city_id,state_id]

There was another table in same database,

Registration[reg_id,city_id]

Now, i have added one more column into 'Registration' table, so it is become as shown below,

    Registration[reg_id,city_id,state_id]

But, the problem is that values of the state_id column is '0'. So, How can i insert value of state_id column of "Registration" table from the "City" table according to matching city_id value of "Registration" table.

Upvotes: 1

Views: 1246

Answers (3)

esengineer
esengineer

Reputation: 9918

This will work in MySQL. In the background it instruct the server to make inner join on two tables.

UPDATE Registration r, City c SET r.state_id = c.state_id WHERE r.city_id = c.city_id;

Upvotes: 1

nidu
nidu

Reputation: 559

not sure about MySQL, but that's how it can be done in Oracle and SQL Server:

UPDATE Registration r SET state_id = (SELECT state_id FROM City WHERE city_id = r.city_id)

Upvotes: 0

zerkms
zerkms

Reputation: 254896

As long as this query will be performed just once - you could use this not efficient, but readable query:

UPDATE Registration r
   SET state_id = (SELECT state_id
                     FROM City c
                    WHERE c.city_id = r.city_id)

Upvotes: 0

Related Questions