I'm a complete novice when it comes to SQL queries. Below, I have included a script that I've been working on that is attempting to correct some "bad" data within a table in our production database.

My best explanation of what this table is for is a relationship of two tables, a many to many if I'm not mistaken. I understand what is causing the error, but due to my inexperience, I'm really struggling to figure out how to approach this.

The table at the end of this post is a small subset of the data that I plan on working with but figured it was better to start small before scaling up.

BEGIN TRANSACTION Update100KE74Sequence

UPDATE PointWeight
  SET Weights_Id = CASE Weights_Id
    /* 2K Weights */
    WHEN 53 THEN 56
    WHEN 54 THEN 55
    WHEN 55 THEN 54
    WHEN 56 THEN 53
    
    /* 5K Weights */
    WHEN 57 THEN 61
    WHEN 58 THEN 61
    WHEN 59 THEN 60
    WHEN 60 THEN 59
    WHEN 61 THEN 58
    WHEN 62 THEN 57

    /* 8K Weights */
    WHEN 63 THEN 75
    WHEN 65 THEN 72
    WHEN 66 THEN 71
    WHEN 67 THEN 70
    WHEN 68 THEN 69
    WHEN 69 THEN 68
    WHEN 70 THEN 67
    WHEN 71 THEN 66
    WHEN 72 THEN 65
    WHEN 75 THEN 63
    ELSE Weights_Id
  END
  WHERE PointWeight_Weight_Id > 2087
    AND PointWeight_Weight_Id < 2101
    AND Weights_Id < 76
    AND Weights_Id > 50

ROLLBACK

Error:

Msg 2627, Level 14, State 1, Line 3 Violation of PRIMARY KEY constraint 'PK_PointWeight'. Cannot insert duplicate key in object 'dbo.PointWeight'. The duplicate key value is (2099, 61).

CodePudding user response：

You are assigning both 57 and 58 to 61, i.e. a duplicate key.

/* 5K Weights */
WHEN 57 THEN 61
WHEN 58 THEN 61