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?
CREATE TEMPORARY TABLE tmp SELECT * FROM myTable WHERE cat_uid=10; UPDATE tmp SET cat_uid=11; INSERT INTO myTable SELECT * FROM tmp;
Well, you could cut that down to simply ...
INSERT INTO myTable (cat_id, col1, col2, col3) SELECT 11, col1, col2, col3 FROM myTable WHERE cat_uid = 10;
By not specifying the uid in the INSERT statement it allows MySQL to generate one. And you're bypassing the temporary table, so it's probably at least twice as fast.
On 22 January 2013 10:26, Greg Thomas greg.d.thomas@gmail.com wrote:
Well, you could cut that down to simply ...
INSERT INTO myTable (cat_id, col1, col2, col3) SELECT 11, col1, col2, col3 FROM myTable WHERE cat_uid = 10;
Very true, well spotted. I'm sure there was a reason I went via a temporary table initially..
However, ideally what I'd like is to not have to specify col1, col2, col3 (so that the code is as agnostic as possible to table schema changes). Pretty much by definition here, if the table schema changes to add a field then that field should be included in the copy, not silently ignored as will be the case if I list the fields.
That said, at present my application uses a MySQL user which does not have ALTER TABLE privileges so my solution doesn't work either unless I add the privilege, which I don't want to do. (Seems daft that if I give the user permission to create temporary tables then it can drop them but not alter them, but them's the rules...) Given this limitation, your solution is better than mine, so thanks for that.
However, ideally what I'd like is to not have to specify col1, col2, col3 (so that the code is as agnostic as possible to table schema changes). Pretty much by definition here, if the table schema changes to add a field then that field should be included in the copy, not silently ignored as will be the case if I list the fields.
Well, you could poke around in information_schema to work out the columns WHERE table_name = 'myTable' AND column_name <> 'uid' and build up the SQL programmatically if it's a genuine concern. I forget the name of the tables and columns, but from memory it's pretty self explanatory.
Greg
On 22 January 2013 11:41, Greg Thomas greg.d.thomas@gmail.com wrote:
Well, you could poke around in information_schema to work out the columns WHERE table_name = 'myTable' AND column_name <> 'uid' and build up the SQL programmatically if it's a genuine concern. I forget the name of the tables and columns, but from memory it's pretty self explanatory.
I did find some examples of this but decided to KISS and go with your first suggestion. Thanks!
-
Greg Thomas -
Mark Rogers