Update a key column

[urya]

When I run "update MYTABLE123 SET ColumnA=ColumnB"
I get the following message.

Msg 2627, Level 14, State 1, Server MYSERVER\MYSQL, Line 1
Violation of PRIMARY KEY constraint 'MYTABLE123'. Cannot insert duplicate key in
object 'MYTABLE123'.
The statement has been terminated.

The constraint says "CREATE UNIQUE" & "Constraint".
I cannot modify that value. (and I don't think I should)

I can manually update the column by hand but I just cannot update all at once.
I have thousands of records which I cannot do it manually...

Plase help.

Thanks.

Urya

 

[r937]

I can manually update the column by hand

not if it violates a primary key or unique constraint, you can't

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[JamesLean]

So, you have a unique constraint on ColA but there are duplicate values in ColB. You need to fix these in order to insert them into ColA.

To find the duplicates:

SELECT ColB, COUNT(*)
FROM t
GROUP BY ColB
HAVING COUNT(*) > 1

--James

 

[urya]

James,

Thanks.
I'll try to eliminate duplicates...
I pretty much know where they are...
I guess my only way is to copy the duplicate column(colB) to a new table (colB'), replace the colB with something else, and copy colB' back to colA.

So to copy back ColB' to ColA,
I should:
UPDATE TableA
SET ColA = TableB.ColB
WHERE TableA.ID = TableB.ID

Would this work?

Urya