{"id":3073,"date":"2023-01-29T08:29:00","date_gmt":"2023-01-29T16:29:00","guid":{"rendered":"https:\/\/www.wellgolly.com\/?p=3073"},"modified":"2023-01-29T08:29:00","modified_gmt":"2023-01-29T16:29:00","slug":"sql-for-managing-wordlists","status":"publish","type":"post","link":"https:\/\/www.wellgolly.com\/?p=3073","title":{"rendered":"SQL for managing wordlists"},"content":{"rendered":"<p>A long time ago I started a notebook where I would record passages from books. Most of them are now in the <a hrf='https:\/\/www.wellgolly.com\/Quotes\/'>quotes<\/a> database. I also started collecting <a href='https:\/\/www.learningfundamentals.com\/words\/favorites.php'>interesting words<\/a>. The first word whose definition I recorded was fatalism: belief that everything is controlled by fat. The first serious words were bellicose: warlike and ameliorate: to make or desire to be better, improve. There are now 1896 interesting words in the database, along with examples of usage.<\/p>\n<p>As part of an app that we were making we assigned a <a href='https:\/\/www.learningfundamentals.com\/words\/words_by_level.php'>difficulty level<\/a> to words. There are over 16,000 words that have a difficulty level assigned to them. The code to assign levels to is straightforward since the tables are in the same database.<\/p>\n<p><pre><code class=\"preserve-code-formatting\">\nUPDATE favorite_words, words_by_level \nSET favorite_words.level = words_by_level.level\nWHERE favorite_words.word = words_by_level.word\n<\/code><\/pre><\/p>\n<p>Even though only one table is being updated, you need to mention both in the UPDATE section of the code.<\/p>\n<p>I have another project where I am collecting words and quotes from Nero Wolfe stories. In that case I want to make sure that every word in that table has a definition in the favorite words table. I\u2019m running the code in the Wolfe database and the favorite word table is in a different database so I need to make sure that I prefix the tables in the other database with the database name.<\/p>\n<p><pre><code class=\"preserve-code-formatting\">\nSELECT NWquote.word FROM NWquote \nLEFT JOIN words.favorite_words \nON words.favorite_words.word = NWquote.word \nWHERE words.favorite_words.word IS NULL<\/code><\/pre><\/p>\n<p>I looked for words in my Wolfe database that did not have a level in my favorite words database and manually updated the level in the favorite words database.<br \/>\n<pre><code class=\"preserve-code-formatting\">\nSELECT DISTINCT NWquote.word FROM NWquote\nLEFT JOIN words.favorite_words\nON words.favorite_words.word = NWquote.word\nWHERE words.favorite_words.level = 0\nORDER BY NWquote.word<\/code><\/pre><\/p>\n<p>Surprisingly, around two hundred of my favorite words still did not have a difficulty level assigned so after manually checking them I assigned the rest to level 6.<br \/>\n<pre><code class=\"preserve-code-formatting\">\nUPDATE words.favorite_words, NWdatabase.NWquote\nSET words.favorite_words.level = 6 \nWHERE words.favorite_words.word = NWdatabase.NWquote.word \nAND words.favorite_words.level = 0\n<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A long time ago I started a notebook where I would record passages from books. Most of them are now in the quotes database. I also started collecting interesting words. The first word whose definition I recorded was fatalism: belief that everything is controlled by fat. The first serious words were bellicose: warlike and ameliorate: &hellip; <a href=\"https:\/\/www.wellgolly.com\/?p=3073\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">SQL for managing wordlists<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-3073","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/3073","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3073"}],"version-history":[{"count":5,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/3073\/revisions"}],"predecessor-version":[{"id":3078,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/3073\/revisions\/3078"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3073"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3073"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3073"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}