copying over a record EXCEPT FOR the primary key

[sfullman]

If two tables are the same, I can do this:

INSERT INTO same_structure_table
SELECT * FROM first_table WHERE ID=121

Where 121 is the id of the record I want to copy.

I can then change the ID and move back as a new record into the first_table.

Is there a quicker way to do this WITHOUT listing field names (using * instead)? So that you copy the record into the SAME table, but not the ID, and let auto_increment do its thing?

That would be VERY COOL.

Thanks if you know the answer.

Sam

[LordShryku]

Nope. * says select all fields. If you wanted to skip a field, you'd have to select the specific fields. Though, if it's just a want to keep it dynamic, and you're using php for it, you could just get the field names into an array, unset the primary key, then implode. There's your field list.

[sfullman]

hmm.. I thought there was something like UPDATE INTO which would insert or update depending on primary or unique key conditions.

One thing mysql is not good at structurally is EXCEPT in this application and one other. I wish I could grant a user rights to a database, to all tables EXCEPT certain ones. That way I don't have to do anything when I add a new table. Same concept here, except with permissions. But the mysql databse doesn't allow for this type of thing. Too bad.

Thanks though 🙂