SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast Kymira's Avatar
    Join Date
    Sep 2002
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help normalizing pre-existing DB

    We have a table that contains articles. In the table, there is a field that specifies which section a particular article belongs to, however, it is NOT a foreign key to another table, but is just a text field (bad, I know, I didn't design the DB).

    My question is, is there a query I can run that would automatically replace the section field in the article table with the id of a section from the section table?

    This is what the article table is currently defined as (MySQL table). I've taken out fields that are not pertainant to this question:

    Code:
    CREATE TABLE story_table (
      headline text,
      subhead text,
      summary text,
      section text NOT NULL,
      id int(11) unsigned NOT NULL default '0',
      PRIMARY KEY  (id),
      UNIQUE KEY id (id)
    ) TYPE=ISAM;
    This is the proposed section table:

    Code:
    create table section (
    	id int unsigned NOT NULL AUTO_INCREMENT,
    	db_name tinytext,
    	display_name tinytext,
    	description tinytext,
    	order int unsigned default '0',
    	PRIMARY KEY (id)
    );

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you want to do is this?

    1.
    Create a new column "section_id" in story_table

    2.
    update story_table
    set section_id = section.id
    from story_table inner join section
    on story_table.section = section.display_name

    Step 2 will not work in MySql

    Alternative solution:
    Create a new table "story_table_new" and...
    insert into story_table_new
    (id, headline, ..., section_id)
    select story_table.id, story_table.headline, ..., section.id)
    from story_table inner join section
    on story_table.section = section.display_name

    (Then drop story_table and rename story_table_new)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •