Page 1 of 1
Copy Partial Dataset
Posted: Mon Jul 05, 2010 6:28 pm
by JakeJ
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?
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 11:04 am
by josh
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
Posted: Wed Jul 07, 2010 12:53 pm
by JakeJ
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
Posted: Wed Jul 07, 2010 1:39 pm
by Weirdan
Code: Select all
create table `newTable` like `oldTable`;
insert into `newTable` select * from `oldTable` where condition..
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 1:49 pm
by JakeJ
Ah, see I didn't know about the LIKE statement. Thanks!!!
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 5:09 pm
by josh
(thats a mysql only solution...)
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 5:50 pm
by JakeJ
Sure, but it's exactly what I needed.
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 7:47 pm
by josh
weirdan you'll have to teach me to read minds

The force is strong with ye
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 9:32 pm
by Weirdan
josh wrote:weirdan you'll have to teach me to read minds

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'.
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 9:54 pm
by josh
Didn't know that. Thanks for sharing
Re: Copy Partial Dataset
Posted: Wed Jul 07, 2010 10:25 pm
by JakeJ
Weirdan wrote:josh wrote:weirdan you'll have to teach me to read minds

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'.
And if I was talking about **choke** MS Access, I'd be talking about auto number.