Any MySQL gurus here?
I have a table: `item_uid` INT (Primary key) `cat_uid` INT <several other fields>
What I want to do is take all records where cat_uid=10, and insert them back into the table with cat_uid=11 (in other words duplicating "category 10" as "category 11".
What I had was: CREATE TEMPORARY TABLE tmp SELECT * FROM myTable WHERE cat_uid=10; UPDATE tmp SET cat_uid=11; INSERT INTO myTable SELECT * FROM tmp;
This used to work when I didn't have the primary key `uid`, but now this just attempts to insert duplicates for the uid field and fails.
The following works instead: CREATE TEMPORARY TABLE tmp SELECT * FROM myTable WHERE cat_uid=10; ALTER TABLE tmp CHANGE uid uid INT(11) NULL DEFAULT NULL; UPDATE tmp SET uid=NULL, cat_uid=11; INSERT INTO myTable SELECT * FROM tmp; In other words, alter the tmp table structure to allow the uid field to be NULL, and as part of my update set all the uid's to NULL, so that the final insert tries to insert NULL for uid which causes MySQL to insert the next auto-increment value instead.
But this seems very convoluted, is there a "correct" (or just "better") way?