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]
