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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
