On Friday 02 July 2004 11:40 pm, Your Name wrote:
> ... I now realize the importance of normalizing my
> data,
>... what [is] the easy way... of getting
> it done now that I already have a database set up.
>
> Suppose I have a table "books", consisting of "id",
> "title", and "publisher"; the "publisher" is
> redundant. I'd like to create a "publisher" table
> consisting of "id" and "publisher", and then I want
> my original "books" table to use its "publisher"
> column to hold the value of "publisher.id" instead
> of the publisher's name.
>
Here's a 3-a.m.-I-really-shouldn't-be-doing-this-now
stab at it:
Extract the publisher names from books into a new
publisher table:
create table publisher
select distinct publisher from books;
Add a primary key to the new publisher table:
alter table publisher
add column id int primary key auto_increment;
Use a join to create a new table with both publisher
and publisher id:
create table newBooks
select books.*, publisher.id as publisherId
from books, publisher
where books.publisher = publisher.publisher;
Drop the redundant column publisher:
alter newBooks drop column publisher;
rename table books to oldBooks;
rename table newBooks to books;
There's bound to be better ways to do it. I'll check
back tomorrow to see what I can learn from others'
suggestions.
Regards,
John
On Friday 02 July 2004 11:40 pm, Your Name wrote:
> I'm learning more about SQL all the time--I'm new to
> things now but I'm trying to read. I now realize
> the importance of normalizing my data, but what I
> can't figure out is what the easy way is of getting
> it done
> now that I already have a database set up.
>
> Suppose I have a table "books", consisting of "id",
> "title", and "publisher"; the "publisher" is
> redundant. I'd like to create a "publisher" table
> consisting of "id" and "publisher", and then I want
> my original "books" table to use its "publisher"
> column to hold the value of "publisher.id" instead
> of the publisher's name.
>
> Is there a way I can do this with an existing table
> full of data, other than going through it
> line-by-line with
> a scripting language?
>
> Thanks!
>
> Jen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
