MySQL ODBC Connector - insert into... select... duplicate key

Author

insert into... select... duplicate key

Schimmel LCpl Robert B \

2005-09-25, 3:23 am

------ _=_NextPart_001_01C5
C18B.1AE15B9E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for
the full record. I know that I can use the information_schema to do this
in MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I am looking for a way to select all of the columns in a
record except one, so that the auto-incrementing primary key will
automatically insert itself. Of course, if anyone has any other
suggestions for a work around, that would be good, too.

=20

Rob Schimmel

2d Intel bn

USMC

------ _=_NextPart_001_01C5
C18B.1AE15B9E--

Danny Stolle

2005-09-25, 7:23 am

Hi,

I am hoping you meen this:

You have to use the fields in your into -statement and select statement,
not including the field having the auto-numbering

so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from table1;

autonumbering will automatticaly be applied :-)

Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a
> full record in a table that has a primary key with auto-increment. The
> real problem is that I want the statement to use SELECT * so that if
> columns ever get added to the table the statement will still work for
> the full record. I know that I can use the information_schema to do this
> in MySQL 5, but the server I am currently work with is MySQL 4.
> Basically, I am looking for a way to select all of the columns in a
> record except one, so that the auto-incrementing primary key will
> automatically insert itself. Of course, if anyone has any other
> suggestions for a work around, that would be good, too.
>
>
>
> Rob Schimmel
>
> 2d Intel bn
>
> USMC
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Schimmel LCpl Robert B \

2005-09-25, 7:23 am

That is the effect that I am looking for, but exactly the method that I
am trying to avoid. If I type the column names into my INSERT... SELECT
and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do
it dynamically in order to avoid maintenance of the statement in my
program later.

Rob

Danny Stolle [mailto:danny-boy@chello.nl] wrote:

Hi,

I am hoping you meen this:

You have to use the fields in your into -statement and select statement,

not including the field having the auto-numbering

so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from table1;

autonumbering will automatticaly be applied :-)

Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a
> full record in a table that has a primary key with auto-increment. The
> real problem is that I want the statement to use SELECT * so that if
> columns ever get added to the table the statement will still work for
> the full record. I know that I can use the information_schema to do
this
> in MySQL 5, but the server I am currently work with is MySQL 4.
> Basically, I am looking for a way to select all of the columns in a
> record except one, so that the auto-incrementing primary key will
> automatically insert itself. Of course, if anyone has any other
> suggestions for a work around, that would be good, too.
>=20
> =20
>=20
> Rob Schimmel
>=20
> 2d Intel bn
>=20
> USMC
>=20
>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Danny Stolle

2005-09-25, 7:23 am

Well I haven't realy found a select method in which you can isolate a
field. Like a complementary method, in which you select like one field,
but shows the fields except the field which you have used in your
select-statement.

So you excually want to dynamically insert the records, not knowing how
many fields you excually have; excluding the auto-numbering field.
Wouldn't it be better to use PHP or another API in which you retrieve
the fields and create an SQL statement using these variables and having
the knowledge of creating the sql-statement?

You could also try to automate an export and use the load datafile to
import the information; but then again you have to rewrite the datafile.

Best regards, Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> That is the effect that I am looking for, but exactly the method that I
> am trying to avoid. If I type the column names into my INSERT... SELECT
> and someone later adds a column to the table, I would have to go back
> into my program and update the statement. I am looking for a way to do
> it dynamically in order to avoid maintenance of the statement in my
> program later.
>
> Rob
>
>
> Danny Stolle [mailto:danny-boy@chello.nl] wrote:
>
> Hi,
>
> I am hoping you meen this:
>
> You have to use the fields in your into -statement and select statement,
>
> not including the field having the auto-numbering
>
> so if e.g. field1 has autonumbering ->
> insert into table1 (field2, field3) select (field2, field3) from table1;
>
> autonumbering will automatticaly be applied :-)
>
> Danny
>
> Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
>
>
> this
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Schimmel LCpl Robert B \

2005-09-25, 7:23 am

I am using Cold Fusion and as I stated in my original message, if I were
using MySQL 5, then I could use information_schema to retrieve the
column names in the table and do it with variables in Cold Fusion. I do
that on all my pages on the MySQL 5 servers with which I work. However,
the server I am working with currently is MySQL 4 and I am unaware of
any way to retrieve the column names from a table in MySQL 4.

Rob

Danny Stolle [mailto:danny-boy@chello.nl] wrote:

Well I haven't realy found a select method in which you can isolate a=20
field. Like a complementary method, in which you select like one field,=20
but shows the fields except the field which you have used in your=20
select-statement.

So you excually want to dynamically insert the records, not knowing how=20
many fields you excually have; excluding the auto-numbering field.=20
Wouldn't it be better to use PHP or another API in which you retrieve=20
the fields and create an SQL statement using these variables and having=20
the knowledge of creating the sql-statement?

You could also try to automate an export and use the load datafile to=20
import the information; but then again you have to rewrite the datafile.

Best regards, Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> That is the effect that I am looking for, but exactly the method that
I
> am trying to avoid. If I type the column names into my INSERT...
SELECT
> and someone later adds a column to the table, I would have to go back
> into my program and update the statement. I am looking for a way to do
> it dynamically in order to avoid maintenance of the statement in my
> program later.
>=20
> Rob
>=20
>=20
> Danny Stolle [mailto:danny-boy@chello.nl] wrote:
>=20
> Hi,
>=20
> I am hoping you meen this:
>=20
> You have to use the fields in your into -statement and select
statement,
>=20
> not including the field having the auto-numbering
>=20
> so if e.g. field1 has autonumbering ->
> insert into table1 (field2, field3) select (field2, field3) from
table1;
>=20
> autonumbering will automatticaly be applied :-)
>=20
> Danny
>=20
> Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
>=20
>=20
> this
>=20
>=20
>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Danny Stolle

2005-09-25, 7:23 am

I am not familiour with Cold Fusion but: cant you use 'show columns from
table' ?? and use the result object?

This normally works in e.g. C or PHP

danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> I am using Cold Fusion and as I stated in my original message, if I were
> using MySQL 5, then I could use information_schema to retrieve the
> column names in the table and do it with variables in Cold Fusion. I do
> that on all my pages on the MySQL 5 servers with which I work. However,
> the server I am working with currently is MySQL 4 and I am unaware of
> any way to retrieve the column names from a table in MySQL 4.
>
> Rob
>
> Danny Stolle [mailto:danny-boy@chello.nl] wrote:
>
> Well I haven't realy found a select method in which you can isolate a
> field. Like a complementary method, in which you select like one field,
> but shows the fields except the field which you have used in your
> select-statement.
>
> So you excually want to dynamically insert the records, not knowing how
> many fields you excually have; excluding the auto-numbering field.
> Wouldn't it be better to use PHP or another API in which you retrieve
> the fields and create an SQL statement using these variables and having
> the knowledge of creating the sql-statement?
>
> You could also try to automate an export and use the load datafile to
> import the information; but then again you have to rewrite the datafile.
>
>
> Best regards, Danny
>
>
> Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
>
>
> I
>
>
> SELECT
>
>
> statement,
>
>
> table1;
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Michael Stassen

2005-09-26, 3:23 am

Relevant bits of the conversation so far, with my thoughts at the end:

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a
> full record in a table that has a primary key with auto-increment. The
> real problem is that I want the statement to use SELECT * so that if
> columns ever get added to the table the statement will still work for the
> full record. I know that I can use the information_schema to do this in
> MySQL 5, but the server I am currently work with is MySQL 4. Basically, I
> am looking for a way to select all of the columns in a record except one,
> so that the auto-incrementing primary key will automatically insert
> itself. Of course, if anyone has any other suggestions for a work around,
> that would be good, too.

Danny Stolle wrote:
> You have to use the fields in your into -statement and select statement,
> not including the field having the auto-numbering so if e.g. field1 has
> autonumbering ->
>
> insert into table1 (field2, field3) select (field2, field3) from table1;
>
> autonumbering will automatically be applied :-)

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> That is the effect that I am looking for, but exactly the method that I
> am trying to avoid. If I type the column names into my INSERT... SELECT
> and someone later adds a column to the table, I would have to go back
> into my program and update the statement. I am looking for a way to do it
> dynamically in order to avoid maintenance of the statement in my program
> later.

Danny Stolle wrote:
> So you actually want to dynamically insert the records, not knowing how
> many fields you actually have; excluding the auto-numbering field.
> Wouldn't it be better to use PHP or another API in which you retrieve the
> fields and create an SQL statement using these variables and having the
> knowledge of creating the sql-statement?

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> I am using Cold Fusion ... However, the server I am working with
> currently is MySQL 4 and I am unaware of any way to retrieve the column
> names from a table in MySQL 4.

Danny Stolle wrote:
>
> I am not familiar with Cold Fusion but: cant you use 'show columns from
> table' ?? and use the result object?
>
> This normally works in e.g. C or PHP

That should work, but seems a lot of effort. Another option would be to use
a temporary table to store the row(s) to be copied. Assuming the
auto_increment column is named id, it would look something like this:

# select the row(s) to be copied into a temp table
CREATE TEMPORARY TABLE dupe SELECT * FROM yourtable WHERE {conditions};

# change the id column to allow NULLs
ALTER TABLE dupe CHANGE id id INT;

# change the id(s) to NULL
UPDATE dupe SET id=NULL;

# copy the rows back to the original table
INSERT INTO yourtable SELECT * FROM dupe;

# clean up
DROP TABLE dupe;

This works because inserting a row with a NULL in the auto_increment id
column works the same as leaving the column out.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Schimmel LCpl Robert B \

2005-09-26, 3:23 am

Actually, both of your solutions worked. Thanks much for the input guys.

Rob

-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@verizon.net]=20
Sent: Monday, September 26, 2005 12:20 AM
To: Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master)
Cc: Danny Stolle; mysql@lists.mysql.com
Subject: Re: insert into... select... duplicate key

Relevant bits of the conversation so far, with my thoughts at the end:

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a=20
> full record in a table that has a primary key with auto-increment. The

> real problem is that I want the statement to use SELECT * so that if=20
> columns ever get added to the table the statement will still work for
the
> full record. I know that I can use the information_schema to do this
in
> MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I
> am looking for a way to select all of the columns in a record except
one,
> so that the auto-incrementing primary key will automatically insert
> itself. Of course, if anyone has any other suggestions for a work
around,
> that would be good, too.

Danny Stolle wrote:
> You have to use the fields in your into -statement and select
statement,=20
> not including the field having the auto-numbering so if e.g. field1
has
> autonumbering ->
>
> insert into table1 (field2, field3) select (field2, field3) from
table1;
>
> autonumbering will automatically be applied :-)

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> That is the effect that I am looking for, but exactly the method that
I=20
> am trying to avoid. If I type the column names into my INSERT...
SELECT=20
> and someone later adds a column to the table, I would have to go back=20
> into my program and update the statement. I am looking for a way to do
it
> dynamically in order to avoid maintenance of the statement in my
program
> later.

Danny Stolle wrote:
> So you actually want to dynamically insert the records, not knowing
how=20
> many fields you actually have; excluding the auto-numbering field.=20
> Wouldn't it be better to use PHP or another API in which you retrieve
the
> fields and create an SQL statement using these variables and having
the
> knowledge of creating the sql-statement?

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> I am using Cold Fusion ... However, the server I am working with
> currently is MySQL 4 and I am unaware of any way to retrieve the
column
> names from a table in MySQL 4.

Danny Stolle wrote:
>=20
> I am not familiar with Cold Fusion but: cant you use 'show columns
from=20
> table' ?? and use the result object?
>=20
> This normally works in e.g. C or PHP

That should work, but seems a lot of effort. Another option would be to
use=20
a temporary table to store the row(s) to be copied. Assuming the=20
auto_increment column is named id, it would look something like this:

# select the row(s) to be copied into a temp table
CREATE TEMPORARY TABLE dupe SELECT * FROM yourtable WHERE
{conditions};

# change the id column to allow NULLs
ALTER TABLE dupe CHANGE id id INT;

# change the id(s) to NULL
UPDATE dupe SET id=3DNULL;

# copy the rows back to the original table
INSERT INTO yourtable SELECT * FROM dupe;

# clean up
DROP TABLE dupe;

This works because inserting a row with a NULL in the auto_increment id=20
column works the same as leaving the column out.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links