Actually, both of your solutions worked. Thanks much for the input guys. Rob
-----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] 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 > 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/[EMAIL PROTECTED]
