MySQL Updating a database table from another table

: Joel Lipman; MySQL; 22 October 2014; Hits: 4412

What?
A quick article on how to populate a database column from another table using a string comparison.

Why?
I have several database tables which replicate country names and I would rather they all use the ccTLD two letter code. This article was written because it took me so long to work it out.

How?

UPDATE  `table_to_update` a
        INNER JOIN `table_to_read` b
            ON a.`CountryName` COLLATE utf8_general_ci LIKE b.`CountryName` COLLATE utf8_general_ci
SET     a.`ccTLD` = b.`ccTLD`

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Generate a Timesheet in MySQL (2832) Joel Lipman (MySQL)
What? This article is to remind me how to create a blank weekly timesheet which reads the duration of events from a...
Access MySQL databases using Oracle SQL Developer (19386) Joel Lipman (MySQL)
What? So this is a quick article on how to install the mySQL add-on for Oracle SQL Developer v3.0.04. Why? I've used...
mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect (159923) Joel Lipman (MySQL)
Applies to: MySQL Database v5.0.45 MySQL Workbench v6.0.8.11354 build 833 What? This is a quick article on how to get...
MySQL Commands to display all columns (6155) Joel Lipman (MySQL)
The scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across...
Error 1065: Query was Empty (7145) Joel Lipman (MySQL)
What? In view of the fact that this error pops up in so many systems I take over, I have often gone down the wrong...
MySQL parameters in Excel 2007 PivotTables (7951) Joel Lipman (MySQL)
Hopefully the title doesn't put you off but after much Googling and Bing-ing, I still couldn't figure out how to do...
MySQL Updating a database table from another table (4412) Joel Lipman (MySQL)
What? A quick article on how to populate a database column from another table using a string comparison. Why? I have...