Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

INSERT INTO SELECT FROM

 
Russell Peters
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table with about 50 columns in it. I want to copy a row and insert it in the same table. Of course I need to change the primary key before inserting. How can I do this so i dont have to type all the 50 columns names?
This is what I have so far:


where Col1 is the primary key. Is there a way i can use the * construct and still be able to change the primary key value?


Thanks
[ October 16, 2006: Message edited by: Russell Peters ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about if you add an extra row to the copied to table. So you can copy all 50 columns, and the 51st is the new tables actual PK.

Mark
 
Russell Peters
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Mark Spritzler:
How about if you add an extra row to the copied to table. So you can copy all 50 columns, and the 51st is the new tables actual PK.

Mark


I am sorry, I don't quite understand what you are saying. The 'copied from' and the 'copied to' tables are the same. I want to add the entire row again except that I change the primary key. What I have already, works. I was just wondering if there was a nicer way to do this without having to type in all 50 column names in the SQL.

Thanks
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought you were creating a record with the column names.

I can't think of anyway to do that because you have to change the primary key.

Mark
 
Russell Peters
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Mark Spritzler:
I thought you were creating a record with the column names.

I can't think of anyway to do that because you have to change the primary key.

Mark


yeah, thanks however, i guess i was just being lazy
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Russell Peters:


yeah, thanks however, i guess i was just being lazy


Lazy programmers are usually the best programmers.

Mark
 
James Ruddy
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Russell Peters:
I have a table with about 50 columns in it. I want to copy a row and insert it in the same table. Of course I need to change the primary key before inserting. How can I do this so i dont have to type all the 50 columns names?
This is what I have so far:


where Col1 is the primary key. Is there a way i can use the * construct and still be able to change the primary key value?


Thanks

[ October 16, 2006: Message edited by: Russell Peters ]




For those who are interested - one way to easily do this is to use a dummy table.

CREATE TABLE MC.MYTRANS_TEMP AS (SELECT * FROM mc.mytrans where myoi = 1111111);

update mc.mytrans_temp set myoi = 222222

insert into mc.mytrans (select * from mc.mytrans_temp where myoi = 222222)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic