Update Table from Another Table

[delusion7]

Hi..

I have 2 tables: country and ticket
country table contains countryId and countries
ticket table contains many fields, and a country field

the country table is new and consists of all countries to be used in a drop down on the ui, that is joined with the ticket table to display the correct country based on the id.

that being said.. the current ticket table also contains a country field which will be eliminated in the future. what I want to do is update the ticket table by finding the closest match to ticket.country in the countries.country table and then update the ticket.countryId to the countries.countryId.

The only problem is, users have entered in varying names for countries. Example: USA, U.S.A, America instead of United States of America.

This is what i have been doing country by country:

update [Ticket].[Ticket]
set [Ticket].[Ticket].[CountryId] = '1'
where [Ticket].[Ticket].[Country] like 'USA'

I know there has to be an easier way to do this. Or is this pretty much the only way, and am I going to have to write a separte update for each country?

[Atli]

Hi.

You can write a UPDATE query that updates all rows in the Ticket table so that the CountryID matches the CountryName in the Country table.

For example:

UPDATE `Ticket` AS t
SET t.`CountryID` = (
    SELECT c.`ID` FROM `Country` AS c
    WHERE c.`Name` = t.`CountryName`
    LIMIT 1
);

However, this will not take into account names that have been entered incorrectly, or in an alternate way, like in your example.

To fix that, you could try simply editing the CountryName field in the Ticket table, replacing known alternatives with the real value before updating the ID.

For example:

UPDATE `Ticket` AS t
SET t.`CountryName` = 'USA'
WHERE t.`CountryName` IN(
    'U.S.A', 'US', 'The United States',
    'United States', 'America'
);

And then run the previous update again.

And as always, before doing any of this, you should back up your data. Just in case.