Copy Partial Dataset

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Copy Partial Dataset

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Copy Partial Dataset

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Copy Partial Dataset

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Copy Partial Dataset

Post by Weirdan »

Code: Select all

create table `newTable` like `oldTable`;
insert into `newTable` select * from `oldTable` where condition..
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Copy Partial Dataset

Post by JakeJ »

Ah, see I didn't know about the LIKE statement. Thanks!!!
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Copy Partial Dataset

Post by josh »

(thats a mysql only solution...)
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Copy Partial Dataset

Post by JakeJ »

Sure, but it's exactly what I needed.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Copy Partial Dataset

Post by josh »

weirdan you'll have to teach me to read minds ;-) The force is strong with ye
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Copy Partial Dataset

Post 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'.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Copy Partial Dataset

Post by josh »

Didn't know that. Thanks for sharing
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Copy Partial Dataset

Post 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.
Post Reply