Himani Bansal Himani Bansal - 6 months ago 9
SQL Question

Replacing data of one table using another table in Mysql

I have two table as follows.

Table : city



city_id city_name state_id
1 Cachar 1
2 Darrang 1
3 Nicobar 1



Table 2 : locality



pincode Address city
110020 loni Nicobar
110021 debru Cachar
110024 rogar Cachar
110023 akura Nicobar



in table city i have 1430 rows , containing different cities in India.
And in table locality each city contains near about 100 pincode .

What i want to do? :
i want to replace each city(column) in table : locality with its corresponding city_id in table : city .

Question : How can i do this ? Is there any fast way to this ? I don't want to use any programming language i.e. php or java. Is there any procedure, looping in Mysql ?

Result should looks like this:

Table 2 : locality



pincode Address city
110020 loni 3
110021 debru 1
110024 rogar 1
110023 akura 3


Answer

You can try this.

UPDATE locality JOIN city ON locality.City = city.city_name SET locality.City = city.city_id