I am trying to update a table in SQL. This is the query: ?

N

NewlyWed

New Member
UPDATE POLICY_X_INSURED<br />
SET POLICY_X_INSURED.INSURED_EID = '5712'<br />
FROM POLICY_SUPP<br />
WHERE POLICY_SUPP.POLICY_ID = POLICY_X_INSURED.POLICY_ID<br />
AND POLICY_SUPP.LOCATION_CODE = '5752'<br />
AND INSURED_EID <>'5712'<br />
AND INSURED_EID <>'72703'<br />
<br />
I get back this error<br />
<br />
Server Msg 2601, Level 14, State 3, Line 1<br />
Cannot insert duplicate key row in object 'POLICY_X_INSURED' with unique index 'POLICY_X_INS_PK'.<br />
The statement has been terminated.<br />
<br />
Obviously it has something to do with unique index; the problem is this makes no sense to me, how do i get around this error to update the fields i need to update? The queries should work as I have tested them on a table with the same fields and same data, but I did not put an Index or anything on there...Thanks!<br />
Ok Here is more information...The POLICY_X_INSURED table has two rows, POLICY_ID and INSURED_EID. INSURED_EID is the id that refers back to the entity_id in the entity table. POLICY_X_INSURED and POLICY_SUPP both relate back to the POLICY table by the policy_id. What I am trying to do is update the insured_eid whenever it equals a certain value in the POLICY_SUPP.location_code field. The POLICY_X_INSURED.INSURED_EID field can have multiple rows with the same value as there are only 8 distinct values in the whole table out of over 3000 records. I dont know much about indexes as I have never ran into this problem before. It seems like it would be an easy update. The end result is that the system that this db is attached to will show a different name in the INSURED field of the interface when that particular policy is brought up. Like I said before, i dont know too much about indexes so if more information is needed, let me know. Thanks in advance!<br />
UPDATE POLICY_X_INSURED<br />
SET POLICY_X_INSURED.INSURED_EID = '5712'<br />
FROM POLICY_SUPP<br />
WHERE POLICY_SUPP.POLICY_ID = POLICY_X_INSURED.POLICY_ID<br />
AND POLICY_SUPP.LOCATION_CODE = '5752'<br />
AND INSURED_EID <>'5712'<br />
AND INSURED_EID <>'72703'<br />
<br />
Why do you have these?<br />
AND INSURED_EID <>'5712'<br />
AND INSURED_EID <>'72703'<br />
<br />
You are trying to set insured_Eid=5712 and then you are trying to exclude anything that is NOT equal to insured_EID<>5712 & 72703<br />
<br />
What is Insured_EID is it a primary key?<br />
<br />
Insured_eid refers to an entity table, it is the entity_id. What I am doing in the query is updating where POLICY_SUPP.LOCATION_CODE = '5752'. Out all all of those records, I only want to update the records where INSURED_EID <>'5712'<br />
AND INSURED_EID <>'72703'. I want to keep those values the same, I guess the first one isnt really necessary, but I didnt think it really mattered...<br />
 
W

Wi-Skier

New Member
Well it worked before because the index was not defined so there was no rule enforcing the uniqueness of the field.

What is appears you are doing is you are setting the insured_eid of multiple rows in in POLICY_X_INSURED to the same value. A field with eid in it strikes me as an id field and that had better be unique and that is what the index constraint is trying to tell you.

It is easy to forget to test one's work with all the constraints installed (create table tname as select * from tname@prod doesn't copy the constraints over) which is what is happening to you, your first tests were w/o the primary key constraint and now you have it in.

Hard to advise you as to what you need to do, as it is more of an application problem you are having, that is WHAT ARE YOU REALLY TRYING TO DO.

PS
Indices are database objects that assist in speeding up queries. Unique indices also enforce rules of uniqueness. For example, in a customer table a given value for a customer id can only appear once. The message you give implies ("PK") a primary key violation is occurring.

From what I can gather from your supplemental material you have a table cross referencing "clients" with "policies". I would not be surprised in this case to see that the table you are trying to update (as you say it has two fields both appear to be foreign keys that is they refer to the primary key fields of other tables) has a primary key consisting of both fields. That is, the combination of both fields MUST be unique. That is, any given customer can only hold one occurrence of a given policy.

That is the below table with a primary key made up of Fld1 & Fld2
Fld1 Fld2 comment
===================
1 1 good unique
1 2 good unique
2 1 good unique
1 1 BAD not-unique

attempting to insert or update an existing record to the last one will fail because the uniqueness of the primary key will be violated.
 
E

elmerr

New Member
UPDATE POLICY_X_INSURED
SET POLICY_X_INSURED.INSURED_EID = '5712'
FROM POLICY_SUPP
WHERE POLICY_SUPP.POLICY_ID = POLICY_X_INSURED.POLICY_ID
AND POLICY_SUPP.LOCATION_CODE = '5752'
AND INSURED_EID <>'5712'
AND INSURED_EID <>'72703'

EDIT

"What I am trying to do is update the insured_eid whenever it equals a certain value in the POLICY_SUPP.location_code field."

Try this, what does this do when you run it?

UPDATE POLICY_X_INSURED
SET POLICY_X_INSURED.INSURED_EID = '5712'
WHERE POLICY_SUPP.POLICY_ID = POLICY_X_INSURED.POLICY_ID
AND POLICY_SUPP.LOCATION_CODE = '5752'
AND POLICY_X_INSURED.INSURED_EID <>'5712'
AND POLICY_X_INSURED.INSURED_EID <>'72703'

Is POLICY_SUPP.POLICY_ID in the same DB? If so I think this should work.
 
You must log in or register to reply here.
Top