Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to copy a row of ANY table changing one column

Tags:

sql

copy

row

I need to duplicate one row changing the PK. The table can be different in each client installation, so I can't just enumerate the columns. I've managed to do the following:

INSERT INTO table SELECT * FROM table WHERE PK='value'

but obviously it fails because I'm trying to duplicate the PK.

Then I tried:

INSERT INTO table SELECT 'newValue' AS PK, * FROM table WHERE PK='value'

It also failed, because the column names didn't match.

I know the PK will always be the first column, but I'm not sure it's of much use.

So... Is this possible? Any idea?

like image 269
raven Avatar asked Mar 10 '10 17:03

raven


1 Answers

The only solution is to build the query dynamically by querying for its list of columns and excluding identity column (which is why I'm assuming you wish to skip the PK).

like image 142
Thomas Avatar answered Oct 26 '22 23:10

Thomas