I was hoping someone could improve on my suggestion. But, not seeing any further replies, I guess my solution must be optimal (troll, troll :)
--John On Saturday 03 July 2004 03:34 am, John Hicks wrote: > 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]
