How to add a new column in one table based on values from another table in Mysql?

Question

current

table_users - user_name, city_name
table_cities - city_id, city_name

I want to add a new column in table_users named city_id where city_id needs to get from table_cities.

expected

table_users - user_name, city_name, city_id (value of this needs to get from table_cities)
table_cities - city_id, city_name

is there any easy way to achieve this?

Lukasz Szozda · Accepted Answer · 2019-12-21 13:18:20Z

2

If city name is unique then:

ALTER TABLE table_users ADD COLUMN city_id INT NULL;

UPDATE table_users
SET city_id = (SELECT city_id 
               FROM table_cities 
               WHERE table_users.city_name = table_cities.city_name
               LIMIT 1);

ALTER TABLE table_users
ADD CONSTRAINT FK_table_cities
FOREIGN KEY (city_id) REFERENCES table_cities(city_id); 

ALTER TABLE table_users MODIFY city_id INT NOT NULL; -- optional

answered Dec 21, 2019 at 13:18

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

How to add a new column in one table based on values from another table in Mysql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related