CODEWITHSUNDEEP
sql
Lisa Green
Lisa Green

Reputation: 31

Cannot insert duplicate key row in object with unique index 'SAXXIRPT while updating

I am writing a SQL statement to update a table in SQL from a temp table. I keep getting this error message: Cannot insert duplicate key row in object with unique index 'SAXXIRPT'.

Here is my update statement: 

Update dbo.sat_ser_rpt_itm
SET itm_key_cd = n.itm_key_cd,
  itm_typ_cd  = n.itm_typ_cd,
  ser_id  = n.ser_id ,
  as_of_dt = n.as_of_dt,
  ocrn_nr  = n.ocrn_nr ,
  id_rssd = n.id_rssd,
  ocrn_day_txt = n.ocrn_day_txt ,
  ocrn_dt = n.ocrn_dt ,
  hol_flg = n.hol_flg ,
  ocrn_val_nr = n.ocrn_val_nr 
  from #LookupTable n
  on sat_ser_rpt_itm.id_rssd = n.id_rssd 
AND sat_ser_rpt_itm.as_of_dt = n.as_of_dt 
AND sat_ser_rpt_itm.ser_id = n.ser_id 
and sat_ser_rpt_itm.itm_typ_cd = n.itm_typ_cd 
and sat_ser_rpt_itm.ocrn_nr = n.ocrn_nr 
where t.id_rssd is not null and  t.as_of_dt is not null  and  t.ser_id is not null and  t.itm_typ_cd is not null and t.ocrn_nr is not null

These are my indexes (clustered):

id_rssd, as_of_dt, ser_id, itm_key_cd and ocrn_nr

What's causing this error message?

Upvotes: 2

Views: 15697

Answers (2)

Daniel Westcott
Daniel Westcott

Reputation: 105

Quite late to answer but your problem is probably simple and your key constraint (you don't show that but that's likely your issue) is doing exactly what it should be doing.

When you combine your update statement data into the constrained key, were you to do a select by that key you would find that you return a single result. The solution is to select by the key first and see if it exists and either update that if desired, xor delete the duplicate, or do something else.

so look at the key constraint which is being violated--the message should say 'duplicate key is (something,etc...) Select by that and see if you are not trying to change an existing item into another existing item.

Cheers, Daniel Westcott

Upvotes: 0

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5300

There isn't much ambiguity in the error message: you are setting a duplicate somewhere

  1. The combination already exists and you are trying to insert it again OR
  2. It doesn't exist and you are updating multiple rows with the same combination Or
  3. the overlap: The combination already exists and you are updating multiple rows with the same combination.

I think the problem causing you this Updating multiple rows with same combination

I am not sure about the what is primary key for the table dbo.sat_ser_rpt_itm

Try like this by joining two tables (dbo.sat_ser_rpt_itm,#lookup_table)

Update itm
SET 
   //itm_key_cd = n.itm_key_cd,
   //ser_id = n.ser_id
  itm_typ_cd  = n.itm_typ_cd,
  //as_of_dt = n.as_of_dt,
  //ocrn_nr  = n.ocrn_nr ,
 //id_rssd = n.id_rssd,
  ocrn_day_txt = n.ocrn_day_txt ,
  ocrn_dt = n.ocrn_dt ,
  hol_flg = n.hol_flg ,
  ocrn_val_nr = n.ocrn_val_nr 
  FROM dbo.sat_ser_rpt_itm itm 
  INNER JOIN #LookupTable n
  ON ..................  ( it could be itm.id_rssd = n.id_rssd OR itm.as_of_dt = n.as_of_dt  OR
                           OR  itm.ser_id = n.ser_id OR itm.itm_key_cd = n.itm_key_cd OR 
                            itm.ocrn_nr = n.ocrn_nr )

  WHERE t.id_rssd is not null AND  t.as_of_dt is not null  
  AND  t.ser_id is not null AND  t.itm_typ_cd is not null AND t.ocrn_nr is not null

Upvotes: 2

Related Questions