Update primary key from table in another database

Question

I have two identical tables in two different databases with the same data but they have different primary keys, I need to update these so they have the same key, so what I did was making sure that none of the tables had any key in common and that there were no duplicates

UPDATE db1.dbo.Table
SET db1.dbo.Table.pcol = rightPcol.pcol
FROM db1.dbo.Table
JOIN db2.dbo.Table AS rightPcol ON db1.dbo.Table.2ndIdent = db2.dbo.Table.2ndIdent

this however results in "Violation of PRIMARY KEY Constraint. Cannot insert duplicate key in object"

when adding a where clause to not update any db1 pcol value that existed in the db2 pcol it didn't update anything at all, it does look like it tries to update with the primary key in db1 instead of db2.

any and all help is greatly appreciated!

//fixed minor spelling error :)

Answer 1

To be honest there isn't much to say - the error message pretty much describes what is it wrong.

You are trying to update a PK Column to a value that already exists.

I would double check to make sure that

1. by joining on 2ndIdent you are not creating any duplicates.
2. there really aren't any duplicates in the tables on DB1 and DB2

Your SQL looks fine so it must be an issue with the data.

To check for duplicates you could use something like this:

Select 2ndIndent
From Table
Group By 2ndIdent
Having Count(2ndIndent) > 1

Answer 2

The "pcol" is the primary key column?

You get "Violation of primary key constraint" when the primary key from "rightPcol" is duplicate with any row in table in "db1". You should select duplicatetion from the two tables and resolve the conflict (how it reselove depends on others thing, which you didn't specified).

So the first you should select the duplications:

SELECT T1.pcol
FROM db1.dbo.Table.pcol AS T1
INNER JOIN db2.dbo.Table.pcol AS T2
    ON T1.pcol = T2.pcol