I’m trying to manage my music library using mySQL in combination with Python scripts to keep a full database of all of my music.

Here’s the issue, I keep two libraries: The first “original” library is formatted chaotically, like the following:

The second, “retagged” library, is mostly fixed, so the album “Revolver” by “The Beatles” is tagged appropriately.

If I left join a database of all of my retagged albums to all of my original albums, it will fail to detect that revolver has already been retagged and moved to my retagged library.

What I want to do is left JOIN all my retagged albums (in a table of filepath, artist, album) to my original albums (in a separate table of filepath, artist, album) to create a new table. In this new table, “full album log” (og_filepath, og_artist, og_album, new_album, new_artist, new_filepath) I want to manually input the new tags for certain albums so that the database can correctly identify which albums are actually missing from my retagged library.

Problem: If I fill in a bunch of the table manually, then run the script again, it will destroy the entire table and rebuild it from scratch, erasing all of my manual edits, and needlessly replacing all of the good matches that have already been made as well.

All I really need the comparison to do is the following logic:

IF (there is an artist, album pair in og_album_log which is NOT present in (full_album_log)
    Make a new entry into full_album_log with the og_filepath, og_artist, and og_album from         original_album_log

IF (there is a matching artist, album pair in retagged_album_log, JOIN it to that entry)

How might I go about doing this?

This is what ChatGPT gave me for this prompt:

               INSERT INTO full_album_log (og_root, og_artist,
                      og_album, new_album, new_artist, new_root)
                      SELECT o.root, o.artist, o.album, r.album, r.artist, r.root
                      FROM original_album_log o
                      LEFT JOIN retagged_album_log r ON o.artist = r.artist AND o.album = r.album
                      LEFT JOIN full_album_log f ON o.artist = f.og_artist AND 
                      o.album = f.og_album AND r.artist = f.new_artist AND r.album = f.new_album
                      WHERE f.og_artist IS NULL AND f.og_album IS NULL;

It’s arcane looking to me, I don’t really understand what it’s supposed to be doing and it overwrites all of my manually inputted data.