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?
--
Mark Rogers // More Solutions Ltd (Peterborough Office) // 0844 251 1450
Registered in England (0456 0902) @ 13 Clarke Rd, Milton Keynes, MK1 1LG