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;
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. here are the test tables I created:- mysql> select * from books; +----+-------+------------+-----------+----------+ | ID | TITLE | first_name | last_name | AuthorID | +----+-------+------------+-----------+----------+ | 1 | java | Matthew | Green | 0 | | 2 | jsp | Matthew | Green | 0 | | 3 | linux | Craig | White | 0 | | 4 | word | John | Brown | 0 | | 5 | c++ | Matthew | Green | 0 | +----+-------+------------+-----------+----------+ mysql> select * from authors; +----------+------------+-----------+ | AuthorID | first_name | last_name | +----------+------------+-----------+ | 1 | Matthew | Green | | 2 | John | Brown | | 3 | Craig | White | +----------+------------+-----------+ ----- Original Message ----- From: "Dan Jones" <[EMAIL PROTECTED]> To: "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, August 09, 2003 12:57 AM Subject: Updating table based upon matching field in second table > 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 > > This obviously doesn't work. Any assistance on how to forumulate this > query (or, if I'm headed down the wrong path, the correct way to do this > operation) greatly appreciated. > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.506 / Virus Database: 303 - Release Date: 01/08/2003
