SQL for managing wordlists

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: to make or desire to be better, improve. There are now 1896 interesting words in the database, along with examples of usage.

As part of an app that we were making we assigned a difficulty level 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.

UPDATE favorite_words, words_by_level 
SET favorite_words.level = words_by_level.level
WHERE favorite_words.word = words_by_level.word

Even though only one table is being updated, you need to mention both in the UPDATE section of the code.

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’m 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.

SELECT NWquote.word FROM NWquote 
LEFT JOIN words.favorite_words 
ON words.favorite_words.word = NWquote.word 
WHERE words.favorite_words.word IS NULL

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.

SELECT DISTINCT NWquote.word FROM NWquote
LEFT JOIN words.favorite_words
ON words.favorite_words.word = NWquote.word
WHERE words.favorite_words.level = 0
ORDER BY NWquote.word

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.

UPDATE words.favorite_words, NWdatabase.NWquote
SET words.favorite_words.level = 6 
WHERE words.favorite_words.word = NWdatabase.NWquote.word 
AND words.favorite_words.level = 0