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?

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 )

askegg wrote:
> 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?

Thanks for the reply.

I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
> 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 )
>
> askegg wrote:
> > 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?

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" <andrew.sk...@gmail.com> wrote:
> Thanks for the reply.
>
> I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > 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 )
>
> > askegg wrote:
> > > 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?

On 23 Jan, 23:48, "askegg" <andrew.sk...@gmail.com> wrote:
> 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" <andrew.sk...@gmail.com> wrote:
>
>
>
> > Thanks for the reply.
>
> > I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > > 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 )
>
> > > askegg wrote:
> > > > 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?- Hide quoted text -- Show quoted text -

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?

On Jan 24, 9:29 pm, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 23 Jan, 23:48, "askegg" <andrew.sk...@gmail.com> wrote:
>
>
>
> > 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" <andrew.sk...@gmail.com> wrote:
>
> > > Thanks for the reply.
>
> > > I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > > > 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 )
>
> > > > askegg wrote:
> > > > > 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?- Hide quoted text -- Show quoted text -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?

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

On 24 Jan, 10:57, "askegg" <andrew.sk...@gmail.com> wrote:
> On Jan 24, 9:29 pm, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 23 Jan, 23:48, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > > 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" <andrew.sk...@gmail.com> wrote:
>
> > > > Thanks for the reply.
>
> > > > I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > > > > 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 )
>
> > > > > askegg wrote:
> > > > > > 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?- Hide quoted text -- Show quoted text -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?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
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?

On Jan 24, 10:01 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> On 24 Jan, 10:57, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > On Jan 24, 9:29 pm, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > On 23 Jan, 23:48, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > > > 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" <andrew.sk...@gmail.com> wrote:
>
> > > > > Thanks for the reply.
>
> > > > > I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > > > > > 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 )
>
> > > > > > askegg wrote:
> > > > > > > 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?- Hide quoted text -- Show quoted text -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?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 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?

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).

On 24 Jan, 22:03, "askegg" <andrew.sk...@gmail.com> wrote:
> On Jan 24, 10:01 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
> wrote:
>
>
>
>
>
> > On 24 Jan, 10:57, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > > On Jan 24, 9:29 pm, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > On 23 Jan, 23:48, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > > > > 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" <andrew.sk...@gmail.com> wrote:
>
> > > > > > Thanks for the reply.
>
> > > > > > I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > > > > > > 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 )
>
> > > > > > > askegg wrote:
> > > > > > > > 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?- Hide quoted text -- Show quoted text -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?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 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?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).- Hide quoted text -- Show quoted text -

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

On Jan 25, 8:41 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> On 24 Jan, 22:03, "askegg" <andrew.sk...@gmail.com> wrote:
>
>
>
> > On Jan 24, 10:01 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
> > wrote:
>
> > > On 24 Jan, 10:57, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > > > On Jan 24, 9:29 pm, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > > > On 23 Jan, 23:48, "askegg" <andrew.sk...@gmail.com> wrote:
>
> > > > > > 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" <andrew.sk...@gmail.com> wrote:
>
> > > > > > > Thanks for the reply.
>
> > > > > > > I did find SELECT INTO xxxx, 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" <spiderc...@yahoo.co.uk> wrote:
>
> > > > > > > > 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 )
>
> > > > > > > > askegg wrote:
> > > > > > > > > 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?- Hide quoted text -- Show quoted text -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?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 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?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).- Hide quoted text -- Show quoted text -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

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 CHARSET=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 ostcode, :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,
ostcode => 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, ostcode

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 CHARSET=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 CHARSET=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.