4

I was curious if it was possible to take data from a table, and duplicate it but assign a new primary key

for example, I wish to take data that has a column "question_id" which acts as the unique key for the table, and copy all of the data from the table with that question_id to the same table but with a new question_id.

any thoughts as to if this is possible using SQL?

my database is an ingres database

thanks in advance

John
  • 45
  • 1
  • 5

3 Answers3

5

Sure, something like this should work:

INSERT INTO YourTable (Question_Id, OtherField,...)
SELECT SomeNewQuestionId, OtherField,...
FROM YourTable
WHERE Question_Id = SomeQuestionId

Just replace SomeQuestionId and SomeNewQuestionId with the appropriate values.

sgeddes
  • 60,365
  • 6
  • 54
  • 76
  • ok so for this. the insert statement is my new data i want, then my select statement is from the old data, and in the where clause i must make question_id= my old question_ID and SomeQuestion_ID= new question_ID. – John Nov 12 '13 at 18:14
  • @John -- sounds right. See this fiddle demo: http://sqlfiddle.com/#!2/8d31b/1 -- Best regards. – sgeddes Nov 12 '13 at 18:40
  • the fiddle will help me a lot. now if my new question_id is already generated (its the pregenerated question set number for the person) what would that look like. – John Nov 12 '13 at 18:47
  • @John -- great, glad I could help :D – sgeddes Nov 12 '13 at 18:48
  • I did this.. 'code' CREATE TABLE YourTable (Question_Id int, OtherField varchar(10)); INSERT INTO YourTable VALUES (1, 'Hello'); INSERT INTO YourTable VALUES (2, 'bugger'); INSERT INTO YourTable VALUES (3, ''); INSERT INTO YourTable (Question_Id, OtherField) SELECT 3, OtherField FROM YourTable WHERE Question_Id = 1 'code' and it generated 2 rows with 3 as the question ID. however i want to overwrite row 3 with the data provided. – John Nov 12 '13 at 18:51
  • sorry if this is confusing. in the SQL fiddle it makes 2 rows with question_id = 3, one with null data and one with 'hello'. i want to overwrite where question_id=3 with the info from row 1. – John Nov 12 '13 at 19:05
  • That sounds more like an `UPDATE` -- here's one approach: http://sqlfiddle.com/#!2/24cb5/1 -- Please note, this is Database Specific so depending on which database you're using, this may or may not work. You can also look into using `MERGE` if there will be updates and inserts. Good luck! – sgeddes Nov 12 '13 at 19:23
  • ok,I will have to play around with it.. thank you so very much for all your help! – John Nov 12 '13 at 19:27
3

It's a simple select query.

insert into mytable
 (field2, field3, etc)
select field2, field3, etc
from mytable
where whatever.

This assumes that neither fields 2 nor 3 are the primary key, and that you have an autoincrement table.

Dan Bracuk
  • 20,184
  • 4
  • 24
  • 39
2

Fast forward two years.... :) I think this is the best and simplest way to do this. Inserting a row of data from the same table with a primary key will result in error because primary keys are unique for each row: Let question_id = 100.

INSERT INTO MyTable SELECT * FROM MyTable Where question_id=100;

In PostgreSQL:

ERROR:  duplicate key value violates unique constraint "MyTable_pkey"
DETAIL:  Key (question_id)=(100) already exists.

It is very simple to avoid the duplicate key value:

INSERT INTO MyTable SELECT (SELECT MAX(question_id)+1),Column1,Column2,etc FROM MyTable Where question_id=100;

By using MAX(question_id)+1, you are incrementing the maximum value of MyTable's question_id primary key, then add/copy the data to a new row with a unique question_id value.

Allan Registos
  • 321
  • 2
  • 10