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

Question

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'

I get back this error:

Server: Msg 2601, Level 14, State 3, Line 1

Cannot insert duplicate key row in object 'POLICY_X_INSURED' with unique index 'POLICY_X_INS_PK'.

The statement has been terminated.

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!

Answer 1

Would it be possible to get some sample data on which to base the query on?

Answer 2

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&#039;s work with all the constraints installed (create table tname as select * from tname@prod doesn&#039;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 (&quot;PK&quot;) a primary key violation is occurring.

From what I can gather from your supplemental material you have a table cross referencing &quot;clients&quot; with &quot;policies&quot;. 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 &amp; 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.

Answer 3

UPDATE POLICY_X_INSURED

SET POLICY_X_INSURED.INSURED_EID = &#039;5712&#039;

FROM POLICY_SUPP

WHERE POLICY_SUPP.POLICY_ID = POLICY_X_INSURED.POLICY_ID

AND POLICY_SUPP.LOCATION_CODE = &#039;5752&#039;

AND INSURED_EID &lt;&gt; &#039;5712&#039;

AND INSURED_EID &lt;&gt; &#039;72703&#039;

EDIT:

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

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

UPDATE POLICY_X_INSURED

SET POLICY_X_INSURED.INSURED_EID = &#039;5712&#039;

WHERE POLICY_SUPP.POLICY_ID = POLICY_X_INSURED.POLICY_ID

AND POLICY_SUPP.LOCATION_CODE = &#039;5752&#039;

AND POLICY_X_INSURED.INSURED_EID &lt;&gt; &#039;5712&#039;

AND POLICY_X_INSURED.INSURED_EID &lt;&gt; &#039;72703&#039;

Is POLICY_SUPP.POLICY_ID in the same DB? If so I think this should work.