24 Working with multiple

those columns are in the albums table, and there is no matching record for Eddie Fisher in

the albums table. We can use that to our advantage.

select song, songs.artist, songs.album from songs

left join albums on songs.album=albums.album

where albums.album is NULL

order by artist

There are 1,198 songs that don't have a matching album. If this were important data, we might

want to figure out why. Most likely, some album names have been spelled differently in one table

than they are in the other. Depending on the source of the data and the nature of the differences,

you might make your fixes in MySQL using the update statement, or in the original source and

re import.

Quotes in statements

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

without quotes is the correct one. We need to update the albums 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.  Note  that  in  CocoaMySQL,  you  may  find  it

easier and safer to make the change in the spreadsheet view.

update albums set album="Rocks" where album= "Rocks" 

If you redo the NULL check, there will now be only 1,189 songs without a matching album.

Using IDs

We've  set  up  each  table  with  a  unique  ID.  We  should  be  using  those  rather  than  album

names to relate our tables together. Otherwise, when we change an album name in one table,

we also need to change it in the other. By adding a reference to the unique ID, which never

changes and never has to change, we only have to update the album name in one place.

The way to do this is to duplicate our table, with an added field.

create table newSongs select songs.*, albums.id as albumID

from songs left join albums on songs.album=albums.album

Once we've verified that the new table has what we want, we can rename the old one to

 oldSongs  and the new one to  songs .

Remember that our new table will contain a bunch of songs with no albumID:

select * from newSongs where albumID is NULL