SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Transfer from non-normalized table

    I want to transfer data from a table to multiple tables

    for example...

    from

    table1: id, name, field1, field2

    to

    table2: id (auto_incremented in this table), name
    table3: id, field1
    table4: id, field2

    the ids in tables 3 and 4 have to match the new id given to it in table 2

    i hope i explained this correctly

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    why can't you keep the id value from table 1?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    we are taking a bunch of different website's registration tables and combining them

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    using you specific example above:
    Code:
    alter table table2 add column old_id int unsigned null;
    
    insert table2
         ( old_id
         , name )
    select id
         , name
      from table1;
    
    insert table3
         ( id
         , field1 )
    select table2.id
         , table3.field1
      from table2
      join table3
        on table2.old_id = table3.id;
    and so on....

    you mentioned combining data from multiple sources: it's important that you do each source one at a time and when you move from one source to the next, you have to update table2 set old_id = null otherwise if the sources have any values for id that overlap you'll get incorrect matches.

    when you're completely done, you can delete the old_id column.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahhh, got it. Thanks so much


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
  •