Assigning unique ID to distinct values

[mfyahya]

I have a table `books` with booktitle and authorname columns. In my
applicatoin booktitle has unique values but authorname doesn't. ie an
author can have many books but not the other way around.

I need to add more author info in the database. So I need a new
`authors` table with authorname as a column (along with address, email,
phone etc) and an authorid primary key. Then I need to replace
books.authorname column with a column containing the corresponding
authorids

How can this be done in sql?
Thanks for your help!

Yahya

[Bill Karwin]

mf*****@gmail.com wrote:

I have a table `books` with booktitle and authorname columns. In my
applicatoin booktitle has unique values but authorname doesn't. ie an
author can have many books but not the other way around.
You don't account for books with multiple authors?
I need to add more author info in the database. So I need a new
`authors` table with authorname as a column (along with address, email,
phone etc) and an authorid primary key. Then I need to replace
books.authorname column with a column containing the corresponding
authorids

MySQL offers a convenient multi-table UPDATE syntax to handle such
cases. For example:

ALTER TABLE books ADD COLUMN authorid INTEGER;

UPDATE books, authors
SET books.authorid = authors.authorid
WHERE books.authorname = authors.authorname;

....I hope no two authors have identical names!

ALTER TABLE books DROP COLUMN authorname;

ALTER TABLE books ADD CONSTRAINT FOREIGN KEY (authorid) REFERENCES
authors(authorid);

See also http://dev.mysql.com/doc/mysql/en/update.html

Regards,
Bill K.

[Malcolm Dew-Jones]

mf*****@gmail.com wrote:
: I have a table `books` with booktitle and authorname columns. In my
: applicatoin booktitle has unique values but authorname doesn't. ie an
: author can have many books but not the other way around.

: I need to add more author info in the database. So I need a new
: `authors` table with authorname as a column (along with address, email,
: phone etc) and an authorid primary key. Then I need to replace
: books.authorname column with a column containing the corresponding
: authorids

: How can this be done in sql?
: Thanks for your help!

Assuming the authorname is unique as you say, then simply use it as (part
of) the primary key in any table with an author. There's nothing that
says you have to use a numeric id.

The primary key of the first table is authorname, booktitle.

The primary key of the new table is authorname.

Keeping the author names in sync is no different than keeping numeric
indexes in sync. There are advantages and disadvantages to either
approach, in this case one advantage is that the old table and data needs
no changes, simply add a new table that records author details.
Prepopulate the author table with names from the first table. After that
require an author to be in the author table before adding to the
author-book table.

$0.10

--

This space not for rent.

[mfyahya]

Thanks for the very useful replies!