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]
