That would work, BUT, I'm looking for a way that I don't have to change the code in the application when I change the structure of the table. If I add a column, I want its value copied to the new record, but don't want to have to change the application to do so.
Your query requires me to alter the application everytime I change the table, which I don't always want to do. > -----Original Message----- > From: Henrik Schröder [mailto:[EMAIL PROTECTED] > Sent: Monday, March 29, 2004 3:07 PM > To: 'Eric J. Janus'; 'MySQL' > Subject: RE: INSERT ... SELECT question > Importance: Low > > > 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]
