No, no, no, no need to make it that complicated! :-) If your table has the columns col1, col2, col3, col4, etc, and col1 is the autoincrement column, and you want to duplicate the entire table, something like this should work:
INSERT INTO t1(col2, col3, col4, ...) SELECT col2, col3, col4, ... FROM t1 The trick is that you select rows from your table that contain all columns except the auto_increment column, and then re-insert them without specifying a value for the auto_increment column, thereby assigning them a new value. If you don't want to duplicate the entire table, just add an appropriate where-clause to the above statement. /Henrik -----Original Message----- From: Eric J. Janus [mailto:[EMAIL PROTECTED] Sent: den 29 mars 2004 21:28 To: Matt Chatterley; 'MySQL' Subject: RE: INSERT ... SELECT question Views would be nice. :) That idea sounds like it would work in a single-user envrinoment, which I'm not. I agree, messy. I'll just write a function to build a field list from a specified table leaving out the AUTO_INCREMENT field...I can then do something like this: SQL = "INSERT INTO tbl_name(" + generate_field_list(tbl_name) + ")"; SQL += SELECT " + generate_field_list(tbl_name) & " FROM tbl_name WHERE <where_clause>" Just a little more work that I was hoping for though! Thanks for the help. Eric > -----Original Message----- > From: Matt Chatterley [mailto:[EMAIL PROTECTED] > Sent: Monday, March 29, 2004 2:25 PM > To: 'Eric J. Janus'; 'MySQL' > Subject: RE: INSERT ... SELECT question > Importance: Low > > > Oh, if only there were views!! That would make this easy. Maybe soon > (*please*). :) > > Another approach (more cumbersome) might be to insert the rows you > need to duplicate into a temporary table, update the id_col adding > max(id_col) from > the original table to each, and then to insert from the temporary > table back into the original. > > I don't like that at all, though. It seems messy. Hopefully someone > here can come up with a better idea! > > Cheers, > > > Matt > > -----Original Message----- > From: Eric J. Janus [mailto:[EMAIL PROTECTED] > Sent: 29 March 2004 20:12 > To: Matt Chatterley; 'MySQL' > Subject: RE: INSERT ... SELECT question > > There is only 1 table. I want to replicate a record in a table except > the AUTO_INCREMENT column. > > Your solution would work, but I'd prefer to not have to maintain a > list of columns in the application. Worst case I'll have the > application generate the query based on the table definition, but I > was hoping that MySQL had something built in to make this easier. > > Thanks, > > Eric > > > -----Original Message----- > > From: Matt Chatterley [mailto:[EMAIL PROTECTED] > > Sent: Monday, March 29, 2004 2:11 PM > > To: 'Eric J. Janus'; 'MySQL' > > Subject: RE: INSERT ... SELECT question > > Importance: Low > > > > > > Can you roughly outline the schema of the two tables? > > > > If the key value doesn't have to match from the input to the > final result, > > you can always insert into a list of fields, skipping the > > auto_increment/key column, and they will continue to be generated.. > > > > Assuming you have two tables with id_col, col2, col3: > > > > INSERT INTO table (col2, col3) > > SELECT col2, col3 FROM table2 WHERE id_col=1; > > > > > > Regards, > > > > Matt > > > > -----Original Message----- > > From: Eric J. Janus [mailto:[EMAIL PROTECTED] > > Sent: 29 March 2004 19:37 > > To: MySQL > > Subject: INSERT ... SELECT question > > > > I have a table with just about 100 columns, and I would like to > > duplicate a row exactly, except for one column, which is the > > AUTO_INCREMENT column. > > > > Using 'INSERT INTO tbl_name SELECT * FROM tbl_name WHERE id_col = 1' > > doesn't work, because it produces the following error: 'ERROR 1062: > > Duplicate entry > > '1' for key 1'. > > > > Because I'd like the application using this database to be simpler > > to maintain, I'd prefer to not have to change the code each time a > > field is added...so is there a way to duplicate a row, but still > > have it automatically assigned an value for the AUTO_INCREMENT > > column? > > > > Thanks, > > > > Eric > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
