MySQL / 22 Oct 2014
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`
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`
Working principle
This site exists as a practical knowledge base built from real-world experience across systems, platforms, and technologies since 1999. Much of the content documents solutions, edge cases, and working patterns that were learned through investigation where documentation was incomplete or absent, and is shared to reduce the same friction for others.
Protected notes
Most articles are public. Login is reserved for selected implementation notes.
Discussion
Comments
Questions, corrections and useful additions are reviewed before appearing here.
No comments yet. Start the discussion.