Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: 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 help

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Try

    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
    Last edited by TimS; 10-11-04 at 18:28.

  3. #3
    Join Date
    Oct 2004
    Posts
    3

    didnt work

    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

  4. #4
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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 S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •