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
