Copy Partial Dataset
Moderator: General Moderators
Copy Partial Dataset
I have to take part of an existing table and create a new table with it. Easy enough with "CREATE table SELECT * FROM other_table WHERE x = 1"
When I do that though, the id (auto_increment) field is copied but is not an auto increment field in the the new table.
I don't care if the indexes get reset in the new table (probably preferable), but when I add new records to that table I want them to auto-increment.
So the question then is: How do I create a new table with a subset of data from the other table so that auto_increment works and do it in one operation?
When I do that though, the id (auto_increment) field is copied but is not an auto increment field in the the new table.
I don't care if the indexes get reset in the new table (probably preferable), but when I add new records to that table I want them to auto-increment.
So the question then is: How do I create a new table with a subset of data from the other table so that auto_increment works and do it in one operation?
Re: Copy Partial Dataset
Why does it need to be in one operation? Thats a silly requirement that makes this problem unapproachable. After you're done implementing it you can encapsulate it (write a function) so you can do it in one operation in the future.
Re: Copy Partial Dataset
I guess I was trying avoid multiple alter table statements if possible for better database performance. I also assumed that I was just missing something and figured there was a way to do it in one statement rather than several.
Re: Copy Partial Dataset
Code: Select all
create table `newTable` like `oldTable`;
insert into `newTable` select * from `oldTable` where condition..
Re: Copy Partial Dataset
Ah, see I didn't know about the LIKE statement. Thanks!!!
Re: Copy Partial Dataset
(thats a mysql only solution...)
Re: Copy Partial Dataset
Sure, but it's exactly what I needed.
Re: Copy Partial Dataset
weirdan you'll have to teach me to read minds
The force is strong with ye
Re: Copy Partial Dataset
Ah, that's actually quite easy... if someone says 'auto_increment' he means 'MySQL'. If it was Oracle or PostgreSQL he would be talking about sequences and triggers, if it was MSSql or DB2 he would call it 'identity'.josh wrote:weirdan you'll have to teach me to read minds
Re: Copy Partial Dataset
Didn't know that. Thanks for sharing
Re: Copy Partial Dataset
And if I was talking about **choke** MS Access, I'd be talking about auto number.Weirdan wrote:Ah, that's actually quite easy... if someone says 'auto_increment' he means 'MySQL'. If it was Oracle or PostgreSQL he would be talking about sequences and triggers, if it was MSSql or DB2 he would call it 'identity'.josh wrote:weirdan you'll have to teach me to read minds