CODEWITHSUNDEEP
mysqlsql
GtDriver
GtDriver

Reputation: 65

SQL Change Text To Unique ID Number

i have a MySql database that contains two tables. One table (the 'Country' table) has a list of countries (the columns are unique_id and country_name). The other table (the 'State') table has a list of global regions (equivalent to the U.S' States). Columns in the States table are state_id, state_name, country_name).

I would like to convert all the country names in the 'State' table to the corresponding unique-id in the 'Country' table. Is there a way to do this?

Any help appreciated and thank you in advance.

Upvotes: 2

Views: 521

Answers (2)

Aram Antonyan
Aram Antonyan

Reputation: 159

If you need to select data to see unique id instead of country name you can use inner join

select StateId, StateName, UniqueId from states
INNER JOIN countries
ON states.CountryName = countries.CountryName;

Upvotes: 0

eggyal
eggyal

Reputation: 125865

  1. Add a new column, to hold the foreign key:

    ALTER TABLE State
  ADD COLUMN country_id BIGINT UNSIGNED NULL,
  ADD FOREIGN KEY (country_id) REFERENCES Country (unique_id);  -- optional

    The line marked optional above adds a foreign key constraint, which will only work if: (i) both tables use the InnoDB engine; (ii) State.country_id and Country.unique_id are the same data type; and (iii) Country.unique_id is indexed (it should probably be that table's primary key).

  2. Populate the new column using a multiple-table UPDATE:

    UPDATE State JOIN Country USING (country_name)
SET    State.country_id = Country.unique_id;

  3. Drop the original column:

    ALTER TABLE State
  DROP COLUMN country_name,
  MODIFY country_id BIGINT NOT NULL;   -- optional

    The line marked optional above forces every State to have a country_id.

Upvotes: 6

Related Questions