By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,809 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

Update Table from Another Table

P: 37
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:
Expand|Select|Wrap|Line Numbers
  1. update [Ticket].[Ticket]
  2. set [Ticket].[Ticket].[CountryId] = '1'
  3. 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?
Feb 20 '09 #1
Share this Question
Share on Google+
1 Reply


Atli
Expert 5K+
P: 5,058
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE `Ticket` AS t
  2. SET t.`CountryID` = (
  3.     SELECT c.`ID` FROM `Country` AS c
  4.     WHERE c.`Name` = t.`CountryName`
  5.     LIMIT 1
  6. );
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE `Ticket` AS t
  2. SET t.`CountryName` = 'USA'
  3. WHERE t.`CountryName` IN(
  4.     'U.S.A', 'US', 'The United States',
  5.     'United States', 'America'
  6. );
And then run the previous update again.

And as always, before doing any of this, you should back up your data. Just in case.
Feb 20 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.