Hi, I want to insert a new row in a table with the same row in the same table with a different primary key value. Can you please tell me a different way to do it? I think I can do it using a temporary table but is there any other way?
We need two things to accomplish this: the primary key value of the row we wish to copy, and the primary key value of the row we wish to insert. Let's say these values are 9 and 37 respectively, i.e., we want to copy the row with the primary key value of 9, and we want to insert this row of data into the table with a value of 37 for the primary key.
insert into mytable ( pkey, data1, data2, data3 ) select 37 , data1, data2, data3 from mytable where pkey = 9
If the primary key happens to be an auto_increment or identity column (depending on which database system you're using), we may be happy to allow the new primary key value to be assigned automatically.
insert into mytable ( data1, data2, data3 ) select data1, data2, data3 from mytable where pkey = 9
In this case we don't mention the primary key column while inserting.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.