primary key problem

In table A I have 3 fields making up the primary key

ID
Code
Date

in this table there are many records having the same ID

I need to change the ID in all the records with certain IDs

For example ID 12345 needs to be changed to 54321 in all records with an ID of 12345

I am using a table B to link old IDs with new ones.

the sql statement I used was
update tableA set ID = (Select newID from TableB) where TableA.ID = TableBoldID

Get this error message...Violation of Primary key constraint...cannot insert duplicate key in object.....

how do I change the IDs in all records with ID of 12345, 12346 ...(about 40 records)??

Thanks very much for your helpTry

update a set a.ID = b.newID
-- SELECT a.ID, b.newID
FROM tableA a
JOIN TableB b ON a.ID = b.oldID

Please run only the SELECT first to verify it is about what you want.

NO WARRENTY AS-IS

Tim S|||I get the same error message when I tried

update a set a.ID = b.newID
-- SELECT a.ID, b.newID
FROM tableA a
JOIN TableB b ON a.ID = b.oldID

Thank you for your help|||Try this

update a set a.ID = b.newID
-- SELECT a.ID, b.newID
FROM tableA a
JOIN TableB b ON a.ID = b.oldID
LEFT JOIN tableA a2
ON a.ID = a2.ID AND a.Code = a2.Code AND a.Date = a2.Date
WHERE a2.ID IS NULL

Tim Ssql