Q:

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

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?

A:

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

