Is it possible to update a primary key column?

Question

I was asked to update a SQL Server table's primary key column, the column already has values. On inner joining with few other tables, I had to update the PK column values from another table. It's failing to do so due to duplicates, inserting value 435 to 2 or more PK column.

Any suggestion how it can be done?

UPDATE t SET t.ID = c.NewID FROM Table1 t INNER JOIN Table2 p ON p.ID = t.ID LEFT OUTER JOIN Table3 c ON c.ID = t.ID WHERE c.status = 'Y'

Please post the query you are using, along with the table definitions. — datagod
– datagod, Commented Nov 7, 2017 at 16:29
No, I have not used merge script. Does that work with pk column update? — B Vidhya
– B Vidhya, Commented Nov 7, 2017 at 16:43
It should , i assume there would be duplicates if u have used left join in the query, why would u need to use left join ? — Ven
– Ven, Commented Nov 7, 2017 at 16:44
If primary key constraint error occurs, try to find null values first or duplicates — Ven
– Ven, Commented Nov 7, 2017 at 16:46

marc_s · Accepted Answer · 2017-11-07 18:24:03Z

Let's say your PK is called A, so:

create a new column called B
update table set B = A
do the changes you need to B
go through all the tables you have FK to your table; drop them, and make sure to reflect the data changes;
drop the PK on A
recreate it on B
recreate all the FKs you dropped 3 steps ago on B

Simple :)

I'm joking, as you can see it is very complicated and error prone. You shouldn't have to worry about your PKs values - if you are there is something wrong in your design

Collectives™ on Stack Overflow

Is it possible to update a primary key column?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related