CODEWITHSUNDEEP
mysqlsql
Rakesh Shetty
Rakesh Shetty

Reputation: 4578

Copy from one table to another table

I know how to copy from one table to another table using MySQL:

INSERT INTO original_table
SELECT * FROM temp_table WHERE id = 1

But my situation is different:

I have temp_table and original_table. Both having same column name i.e.,

    id,
    state,
    city,
    address

But temp_table contains real name of state and city i.e, state and city column contain name of state and city. AND I want to copy this table such way that state and city should contain id of that states and city name when it move to original_table. There is meta table for both states and cites.

states :

id 
state_name

cities :

id,
state_id,
city_name

How can I achieve this?

Upvotes: 0

Views: 92

Answers (1)

eggyal
eggyal

Reputation: 126035

Just join your temp_table to the states and cities tables appropriately:

INSERT INTO original_table
  (id, state, city, address)
SELECT t.id, s.id, c.id, t.address
FROM   temp_table t
  JOIN states     s ON s.state_name = t.state
  JOIN cities     c ON c.city_name  = t.city

Upvotes: 3

Related Questions