On Fri, 2003-08-08 at 21:14, Matthew McNicol wrote: > > I have a database of books that was originally created as a flat file. > > Each record has a number of fields, including the authors name. I'm > > trying to convert the database to something a little more efficient. > > I've created a new table (called Authors) of unique authors names and > > assigned each one a unique ID. I've added a new field in the original > > table (called Books) for the author's ID. Now, I need to update the > > original table with the author ID from the Author's table. > > > > Something like this: > > > > UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = > > Authors.AuthorName
> you were close, try:- > > update books, authors set books.AuthorID = authors.AuthorID where books.first_name = > authors.first_name and books.last_name = authors.last_name; Ah, so you have to list both tables in the update clause even though you're only changing one of them. That works like a charm. Thanks! > > Notes.... > - you have loaded the existing flat file into a MySQL table called books. > - you mentioned that you have altered the books table to add a new field called > AuthorID which is good. > - you have created a new table called authors where the unique key is AuthorID. > > so, > - just run the update query, then remove the author name field(s) from the books > table which are no longer needed. Yes, this was my plan. I'll also need to edit the authors table to split the names into first and last. In the flat file, author's name was a single field and editing it first would have ruined the correlation. This leads me to another question. What's the standard way of handling something like the author of a book when you have a collaboration or an anthology with multiple authors? The only way I can think of to handle it is to have a sentinel value that indicates multiple authors, then have another table which lists the primary key of the books table and associates it with multiple authors. For example: BookID AuthorID 15 22 15 39 15 43 27 03 27 94 This would show that book 15 had three authors - 22, 39 and 43 while book 27 had two authors - 03, 94. Of course, this would vastly complicate displaying or printing the database. It would also make it difficult for searches for a particular author to find books with multiple authors, etc. Is there an easier way to design the database which deals with these issues? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
