Professional Web Applications Themes

Splitting(?) data into a new table? - MySQL

Hi All, Firstly I am no database expert, but I know enough to be dangerous :) I have a need to spilt a dataset into multiple related tables, but there does not seem to be an easy way to do this. Eg. I have one large table that contain usernames, address, phone numbers, etc. I want to put the address (say) into a differnet table and leave behind the id of the address in the old table. Can this be done in pure SQL, or do I need to resort to a script?...

  1. #1

    Default Splitting(?) data into a new table?

    Hi All,

    Firstly I am no database expert, but I know enough to be dangerous :)

    I have a need to spilt a dataset into multiple related tables, but
    there does not seem to be an easy way to do this.

    Eg. I have one large table that contain usernames, address, phone
    numbers, etc.
    I want to put the address (say) into a differnet table and leave behind
    the id of the address in the old table.

    Can this be done in pure SQL, or do I need to resort to a script?

    askegg Guest

  2. #2

    Default Re: Splitting(?) data into a new table?

    According to my book, as I knew I'd seen it somewhere, you could use
    SELECT INTO.

    You might want to check the exact format but appears to be:

    SELECT INTO table_name2 (col1,col2,col4)
    field1,field2,field3 FROM table_name1
    WHERE u_choose etc

    or use the INSERT INTO command

    INSERT INTO table_name2 (col1,col2,col4)
    SELECT fields
    FROM table_name1
    WHERE u_choose

    See how you get on - as I am no guru but reading a book :o)

    askegg wrote: 

    UKuser Guest

  3. #3

    Default Re: Splitting(?) data into a new table?

    Thanks for the reply.

    I did find SELECT INTO x, but it only gets me part of the way. It
    will copy the fields and data over to a new table, but does not create
    a primary key or populate the original table.

    For illustration, starting with:
    Members(id, name, phone, fax, email, address, suburb, state,
    postcode)

    I want to end with:
    Members(id, name, phone, fax, email, address_id)
    Address(id, address, suburb, state, postcode)

    Where "address_id" is the foreign key of the Address table. With a bit
    of manipulation we should be able to collapse duplicate addresses so
    all members at the same address share the address_id.

    Many times I have been given a spreadsheet and asked to "make it live
    on the internet". So I often need to manipulate the data to model
    everything correctly. In this example is allows us to easily lookup
    other members that reside at the same address without complex SQL
    (SELECT * from members WHERE
    address_id=the_id_of_the_address_in_question).

    Given that this is a reasonably common thing to do, I thought there
    might be SQL statement to achieve this, or a tools I can use to
    automate it, but I an unable to find anything.

    Andrew

    On Jan 24, 2:28 am, "UKuser" <co.uk> wrote: 



    > [/ref]

    askegg Guest

  4. #4

    Default Re: Splitting(?) data into a new table?

    BTW - I managed to get a script to do the work, but it would be nice to
    know if this sort of thing can be handled by the database (it would be
    MUCH faster).



    On Jan 24, 9:22 am, "askegg" <com> wrote: 





    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref][/ref]

    askegg Guest

  5. #5

    Default Re: Splitting(?) data into a new table?

    On 23 Jan, 23:48, "askegg" <com> wrote: 








    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref][/ref]

    First of all a request, please don't top post. The convention in this
    NG is for bottom posting and it makes it far easier for folks to help
    if the posts are kept in order.

    Now, I can think of a 3/4 stage process that will do this using
    queries. However, there are a couple of questions that I need to ask
    first.
    1) Does your original table have a primary key? If so what is it?
    2) The normal reason for splitting the addresses off to a separate
    table is so that, if 2 records in your first table had the same
    address, then you could have one address record and use its PK in both
    the other records. Is it fair to say that, you are not ding this in
    this case?

    Captain Guest

  6. #6

    Default Re: Splitting(?) data into a new table?



    On Jan 24, 9:29 pm, "Captain Paralytic" <com> wrote: 

    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > NG is for bottom posting and it makes it far easier for folks to help
    > if the posts are kept in order.
    >
    > Now, I can think of a 3/4 stage process that will do this using
    > queries. However, there are a couple of questions that I need to ask
    > first.
    > 1) Does your original table have a primary key? If so what is it?
    > 2) The normal reason for splitting the addresses off to a separate
    > table is so that, if 2 records in your first table had the same
    > address, then you could have one address record and use its PK in both
    > the other records. Is it fair to say that, you are not ding this in
    > this case?[/ref]

    Thanks for the reply and the advice.

    1) Yes, the orginal table does have a primary key: an autoincrementing
    integer called "id"
    2) That's exactly what I am trying to achieve :)

    askegg Guest

  7. #7

    Default Re: Splitting(?) data into a new table?



    On 24 Jan, 10:57, "askegg" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > > NG is for bottom posting and it makes it far easier for folks to help
    > > if the posts are kept in order.[/ref]

    >
    > 1) Yes, the orginal table does have a primary key: an autoincrementing
    > integer called "id"
    > 2) That's exactly what I am trying to achieve :)[/ref]
    Yes, but are you trying to achieve it in this step. I.E. are you
    saying, if 2 sets of address fields are identical, then I will reuse
    the ID now. Or are you simply moving all the current addreses into a
    separate database and for the moment, each is treated as unique?

    Captain Guest

  8. #8

    Default Re: Splitting(?) data into a new table?



    On Jan 24, 10:01 pm, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > saying, if 2 sets of address fields are identical, then I will reuse
    > the ID now. Or are you simply moving all the current addreses into a
    > separate database and for the moment, each is treated as unique?[/ref]

    Sorry if I was not clear : each member with identical addresses will
    use the same address ID (foreign key), so this is not a straight copy.
    Part of the process will be checking for identical addresses and
    removing them, hence the SQL code above (SELECT * from members WHERE
    address_id=the_id_of_the_address_in_question).

    askegg Guest

  9. #9

    Default Re: Splitting(?) data into a new table?

    On 24 Jan, 22:03, "askegg" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > > saying, if 2 sets of address fields are identical, then I will reuse
    > > the ID now. Or are you simply moving all the current addreses into a
    > > separate database and for the moment, each is treated as unique?Sorry if I was not clear : each member with identical addresses will[/ref]
    > use the same address ID (foreign key), so this is not a straight copy.
    > Part of the process will be checking for identical addresses and
    > removing them, hence the SQL code above (SELECT * from members WHERE
    > address_id=the_id_of_the_address_in_question).- Hide quoted text -- Show quoted text -[/ref]

    What would be useful would be for you to post the schemas and some
    sample data and also the script that you wrote. That'll tell me what
    criteria you used to discover duplicate addresses

    Captain Guest

  10. #10

    Default Re: Splitting(?) data into a new table?



    On Jan 25, 8:41 pm, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > > use the same address ID (foreign key), so this is not a straight copy.
    > > Part of the process will be checking for identical addresses and
    > > removing them, hence the SQL code above (SELECT * from members WHERE
    > > address_id=the_id_of_the_address_in_question).- Hide quoted text -- Show quoted text -What would be useful would be for you to post the schemas and some[/ref]
    > sample data and also the script that you wrote. That'll tell me what
    > criteria you used to discover duplicate addresses[/ref]

    The orginial schema looks like this:

    CREATE TABLE `members` (
    `id` mediumint(9) NOT NULL auto_increment,
    `membershipno` varchar(255) default NULL,
    `linkedmemberno` varchar(255) default NULL,
    `salutation` varchar(255) default NULL,
    `surname` varchar(255) default NULL,
    `givennames` varchar(255) default NULL,
    `yearjoined` varchar(255) default NULL,
    `deceased` varchar(255) default NULL,
    `mailaddresse` varchar(255) default NULL,
    `phone` varchar(255) default NULL,
    `fax` varchar(255) default NULL,
    `address` varchar(255) default NULL,
    `notatthisddress` varchar(255) default NULL,
    `suburb` varchar(255) default NULL,
    `state` varchar(255) default NULL,
    `postcode` varchar(255) default NULL,
    `mobileno` varchar(255) default NULL,
    `emailaddress` varchar(255) default NULL,
    `lastrenewalmonth` varchar(255) default NULL,
    `lastrenewaldate` varchar(255) default NULL,
    `renewalmonthprev` varchar(255) default NULL,
    `membershipamtcurrentyear` varchar(255) default NULL,
    `licencenocurrentyear` varchar(255) default NULL,
    `membershipamountpaid` varchar(255) default NULL,
    `licenceamountpaid` varchar(255) default NULL,
    `committeemember` varchar(255) default NULL,
    `committeeposition` varchar(255) default NULL,
    `membershiptype` varchar(255) default NULL,
    `camsmemberno` varchar(255) default NULL,
    `occupation` varchar(255) default NULL,
    `businessname` varchar(255) default NULL,
    `businesaddress` varchar(255) default NULL,
    `businesssuburb` varchar(255) default NULL,
    `businessstate` varchar(255) default NULL,
    `businesspostcode` varchar(255) default NULL,
    `businessphone` varchar(255) default NULL,
    `othermemberships` varchar(255) default NULL,
    `includeingazette` varchar(255) default NULL,
    `activeofficial` varchar(255) default NULL,
    `activecompetitor` varchar(255) default NULL,
    `lastknownevent` varchar(255) default NULL,
    `roleplayed` varchar(255) default NULL,
    `director` varchar(255) default NULL,
    `zero` varchar(255) default NULL,
    `double0` varchar(255) default NULL,
    `sweep` varchar(255) default NULL,
    `startcontrol` varchar(255) default NULL,
    `finishcontrol` varchar(255) default NULL,
    `roadclosure` varchar(255) default NULL,
    `radiorelay` varchar(255) default NULL,
    `results` varchar(255) default NULL,
    `hq` varchar(255) default NULL,
    `trc` varchar(255) default NULL,
    `clubrally` varchar(255) default NULL,
    `clubtrial` varchar(255) default NULL,
    `motorkhana` varchar(255) default NULL,
    `socialevents` varchar(255) default NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1572 DEFAULT CHT=latin1;

    This is the Ruby on Rails migration that ended up handling the
    extraction:

    def self.up
    create_table :addresses, :force => true do |t|
    t.column :addr1, :string
    t.column :addr2, :string
    t.column :suburb, :string
    t.column :state, :string
    t.column :postcode, :string
    t.column :country, :string, :default => "Australia"
    end

    add_column :members, :address_id, :integer

    # Move the address information into the new table and leave behind
    a foreign key
    members = Member.find(:all)

    members.each do |member|
    address = {
    :addr2 => member.address,
    :suburb => member.suburb,
    :state => member.state,
    :postcode => member.postcode
    }
    new_address = Address.find(:first, :conditions => address)
    member.address_id = new_address.nil? ?
    Address.create(address).id : new_address.id
    member.save
    end

    remove_column :members, :address
    remove_column :members, :suburb
    remove_column :members, :state
    remove_column :members, :postcode

    end

    To end up with:

    CREATE TABLE `addresses` (
    `id` int(11) NOT NULL auto_increment,
    `addr1` varchar(255) default NULL,
    `addr2` varchar(255) default NULL,
    `suburb` varchar(255) default NULL,
    `state` varchar(255) default NULL,
    `postcode` varchar(255) default NULL,
    `country` varchar(255) default 'Australia',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    CREATE TABLE `members` (
    `id` mediumint(9) NOT NULL auto_increment,
    `membershipno` varchar(255) default NULL,
    `linkedmemberno` varchar(255) default NULL,
    `salutation` varchar(255) default NULL,
    `surname` varchar(255) default NULL,
    `givennames` varchar(255) default NULL,
    <cut for brevity>
    `address_id` int(11) default NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1572 DEFAULT CHT=latin1;

    As you can see, the addresses match if address, suburb, state and
    postode from the orginal table all match. I have used this approach
    to continue the extractions for suburbs, states and countries.

    Of course, if this can be handled by the database it will be MUCH
    faster.

    Thanks for your help.

    askegg Guest

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. Replies: 4
    Last Post: October 27th, 03:01 PM
  3. Splitting data from one field into many...
    By Batfastad in forum FileMaker
    Replies: 2
    Last Post: November 22nd, 09:42 PM
  4. Replies: 1
    Last Post: July 9th, 03:30 PM
  5. Splitting text from a database table
    By Tux in forum PERL Miscellaneous
    Replies: 1
    Last Post: July 1st, 06:34 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139