List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:September 26 2005 4:19am
Subject:Re: insert into... select... duplicate key
View as plain text  
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
Thread
insert into... select... duplicate keySchimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)25 Sep
  • Re: insert into... select... duplicate keyDanny Stolle25 Sep
RE: insert into... select... duplicate keySchimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)25 Sep
  • Re: insert into... select... duplicate keyDanny Stolle25 Sep
RE: insert into... select... duplicate keySchimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)25 Sep
  • Re: insert into... select... duplicate keyDanny Stolle25 Sep
    • Re: insert into... select... duplicate keyMichael Stassen26 Sep
RE: insert into... select... duplicate keySchimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)26 Sep