One of the “bad” albums is Aerosmith’s “Rocks”. The title of the album in albums has quotes in it, whereas the title of the album in songs does not. Let’s assume that the title with quotes is the correct one. We need to update the songs table and give it the new title.
select * from albums where album='"Rocks"'
I always try to do a select before I do an update, using the same where that I’ll be using in the update. This reduces the risk of completely destroying my data. Look at that where statement. Because the text that we’re looking for contains double quotes, we cannot use double quotes to surround the text. MySQL supports single quotes as well as double quotes to get around this.
If we surround the text with double quotes, we should avoid double quotes in the text. If we surround the text with single quotes, we should avoid single quotes in the text.
If our text has both double quotes and single quotes in it, we can “backquote” the offending quotes. For example, we could have used:
select * from albums where album="\"Rocks\""
Finally, go ahead and update that album.
update songs set album="\"Rocks\"" where album="Rocks"
If you redo the NULL check, there will now be only 1,189 songs without a matching album.
