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.