Violation of PRIMARY KEY constraint.....Cannot insert duplicate key in object 'dbo.ItemAppliesTo'. error

if you want to update "all" records affected, that single value cannot be a primary key value.so, there is some "design" issue there ...please clarify

yes, I want to update all records that are returned from the query:


select tb.name, ta.ResourceTargetGuid from TBLE_B tb, TBLE_A ta where ta.ItemGuid = tb.Guid AND tb.Description = 'Marketing Group'


I've attempted to translate this query into an Update statement. Is there an easier way where I can take the results of this query and update all of the values returned for ResourceTargetGuid with BDF0A71D-A058-480A-80FB-42F3571DBCED as I wanted to do in my Update statement above?

check what columns included into primary key 'PK_TBLE_A'.  

You either need to change definition of this primary key or exclude duplicates from update.

if your PK is defined as  (ResourceTargetGuid, col1, col2)
you need something like this
--------------
UPDATE TBLE_A
SET ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
FROM TBLE_A ta INNER JOIN TBLE_B ta
ON tb.guid = ta.ItemGuid
WHERE tb.Description = 'Marketing Group'
 and not exists (select 1 from TBLE_A where ResourceTargetGuid=  'BDF0A71D-A058-480A-80FB-42F3571DBCED' and col1=ta.col1 and col2=ta.col2)

trofimoval,

Thanks for you help so far.
I ran   exec sp_pkeys 'ItemAppliesTo' to obtain Primary Key definitions for TBLE_A and received the following:


CORP_Database      dbo      TBLE_A      ItemGuid                             1      PK_TBLE_A
CORP_Database      dbo      TBLE_A      ResourceTargetGuid       2      PK_TBLE_A


I proceeded to add the suggested      and not exists (select 1 from TBLE_A where ResourceTargetGuid=  'BDF0A71D-A058-480A-80FB-42F3571DBCED' and ItemGuid = ta.ItemGuid and ResourceTargetGuid = ta.ResourceTargetGuid)    to my UPDATE statement but continue to receive this annoying "Violation of PRIMARY KEY constraint....." error.
I'll be researching further but any additional suggestions are greatly appreciated.

just to add to this I did also try   and not exists (select 2 from TBLE_A where ResourceTargetGuid=  'BDF0A71D-A058-480A-80FB-42F3571DBCED' ...............................................................

When you run

SELECT * FROM TBLE_A
WHERE ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

Is it return anything?

not exists only checks that you do not add duplicates to rows already in DB. This means your update statement produce those.

Query below will show you ItemGuid from table A that will produce duplicates. You need to define what to do with those. those are either duplicates that you want to delete before you do updates or you need additional condition in your update query to define which one needs to be updated and which one needs remain as is

select ItemGuid, min(ResourceTargetGuid ), max( ResourceTargetGuid ), count(*)
from TBLE_B tb join TBLE_A ta on ta.ItemGuid = tb.Guid
where tb.Description = 'Marketing Group'
group by ItemGuid having count(*)>1

Actually, if something to select to check.
it is

SELECT ta.ResourceTargetGuid
FROM TBLE_A ta INNER JOIN TBLE_B tb
ON ta.ItemGuid = tb.Guid
WHERE tb.Description = 'Marketing Group'
AND ta.ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

If there is something return from this query.
sure you can't perform update unless you delete those records first.

What are the columns that make up the Primary Key for your Table A?

Sorry my post was after several of the updates,

Since there is a Record A that already represents the combination of B and this guid than the record already exists.

Are you trying to add another record or just ensure that it exists?

If you run your update this way you won't get the PK violoation but I don't know if it will accomplish what you are ultimately trying to  do

Update A
set ResourceTargetGuid = '....'
from B
inner join A
on b.guid = A.itemGuid
and isnull(A.ResourceTargetGuid,'') != '....'
left join A as t1
on A.itemGuid = t1.ItemGuid
and t1.ResourceTargetGuid = '....'
where b.Description = 'Marketing Group'
and t1.ItemGuid is null

This way you'll only get an A record that does't already exist in a relationship with the value you are setting

If there is a primary key violation, it just means that the operation you attemp would result in create a *logical* redundant information.  Your update statement need to be reviewed to make sure you are not dupplicating the same information (represented by the primary) another time/

Hope this helps...

'BDF0A71D-A058-480A-80FB-42F3571DBCED' already exists in table A, your update results in creating a dupplicate you should not have.  Don't drop the constraint.  Constraints are here for a reason.

Racimo,
 
Thanks for the detail. I attempted to implement  the query above and still receive the "Violation of PRIMARY KEY constraint" error.
Ater running queries per trofimoval, I know there are records in the ResourceTargetGuid column of TBLE_A that have the value of 'BDF0A71D-A058-480A-80FB-42F3571DBCED', I simply do not want to update those records. (deleting these records is not an option)
It appears that any query I've tried so far does not exclude these records in the UPDATE function thus resulting in the "Violation of PRIMARY KEY constraint".

Run my query as a select and see if there multiple A records. If that's the case then you will need to determine which of the A records would need to be updated. It sounds like one b record can relate to many A records and by pj rule only one of these A record can be assigned the guid

<<It appears that any query I've tried so far does not exclude these records in the UPDATE function thus resulting in the "Violation of PRIMARY KEY constraint".>>
Then you need to modify your query so that it does *not* update records that are already exist in the table.  You may use the NO EXISTS, NOT IN operator to do that...

Hope this helps...

>>know there are records in the ResourceTargetGuid column of TBLE_A that have the value of 'BDF0A71D-A058-480A-80FB-42F3571DBCED', I simply do not want to update those records. (deleting these records is not an option)
Could you clarify?

Lets say query below show count of 3 rows for given ItemGuid. Do you want keep all 3 unchanged or update only 1 to new value?
If you can post sample of one of the duplicates and describe what you want, it will help to resolve your problem

select ItemGuid, min(ResourceTargetGuid ), max( ResourceTargetGuid ), count(*)
from TBLE_B tb join TBLE_A ta on ta.ItemGuid = tb.Guid
where tb.Description = 'Marketing Group'
group by ItemGuid having count(*)>1

Indeed one b record can relate to many many A records.   (where b record = b.guid and A record = A.ItemGuid)

I probably need to redefine the criteria and not reference A.ItemGuid.  As all I want to do is:

update all records in the ResourceTargetGuid column of TBLE_A  with value BDF0A71D-A058-480A-80FB-42F3571DBCED but only for those related records in TBLE_B where the 'Description' column = 'Marketing group'. I guess my poblem is I am having a hard time JOINING these 2 tables as TBLE_A contains 3 columns (ItemGuid, ResourceTargetGuid and Created Date) the first 2 columns are both Primary Keys.

>>Indeed one b record can relate to many many A records.   (where b record = b.guid and A record = A.ItemGuid)
This is OK as long as they have different ItemGuid

Primary key on table A require combination of values in column ItemGuid, ResourceTargetGuid be unique.

>> update all records in the ResourceTargetGuid column of TBLE_A  with value BDF0A71D-A058-480A-80FB-42F3571DBCED but only for those related records in TBLE_B where the 'Description' column = 'Marketing group'

Problem is not just join. It is either data - you have duplicates in tableA or design - you primary key is missing some other column, or update
 My advise look on all columns in TBLE_A  that will become duplicate primary key after your update. Start just with one ItemGuid.  Is there any other columns that differentiate those rows? Then look on tableB "where the 'Description' column = 'Marketing group' May be you need some other criteria on this table to exclude duplicate

Thanks for the comments everyone, my battle continues
This issue continues to amaze me.

I went ahead and deleted all records from the ResourceTargetGuid column TBLE_A where the value was BDF0A71D-A058-480A-80FB-42F3571DBCED. Afterwards I verified that no records exist in this table\column with this value and yet I continue to receive the Primary Key constrain error when I try to UPDATE that column. I broke down the query to simply

Update TBLE_A
set ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

and still the Primary Key constrain error occurs.
I went ahead and deleted all instances of 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
in TBLE_B as well with no luck. (thinking the INNER JOIN  of TBBLE_B would require this)
I'm fine with deleting any records with this value and then running the UPDATE if it allows me to get around this Primary Constraint nightmare.

Deleting rows with  "'BDF0A71D-A058-480A-80FB-42F3571DBCED' will not solve this problem!!!

You have duplicate ItemGuid in your table.
You PK is 2 columns, not one ItemGuid +ResourceTargetGuid

 you produce duplicate during updates, updating rows with the same ItemGuid
ItemGuid    ResourceTargetGuid
123            xxx
123            yyy
123            zzz

when you update those rows to 'DF0A71D-A058-480A-80FB-42F3571DBCED' you create duplicate PK row
=

this is sample data and code
---------------------------------
create table #TBLE_A  (ItemGuid int not null, ResourceTargetGuid varchar(10) not null, other int)
aLTER TABLE #TBLE_A ADD   PRIMARY KEY   (ItemGuid,ResourceTargetGuid)
-------------------------
insert into #TBLE_A
select 456, 'xxx',1
union select 456, 'yyy',1
union select 456, 'zzz',1
union select 123, 'xxx',1
union select 123, 'yyy',1
union select 123, 'zzz',1

create table #TBLE_B  (Guid int, Description varchar(20))
insert into #TBLE_B select 123, 'Marketing Group'
union select 456, 'not Marketing Group'
--------------------
update #TBLE_A set ResourceTargetGuid='new value'
-- this will produce pk violation error

-- this code will move dups into #TBLE_A_dups
insert into #TBLE_A_dups
select ta.*  into #TBLE_A_dups from #TBLE_B tb, #TBLE_A ta where ta.ItemGuid = tb.Guid AND tb.Description = 'Marketing Group'
delete ta from #TBLE_B tb, #TBLE_A ta where ta.ItemGuid = tb.Guid AND tb.Description = 'Marketing Group'

select * from #TBLE_A

-- this will move unique data only back to #TBLE_A

insert into #TBLE_A
select distinct ItemGuid, 'new value', other from #TBLE_A_dups

Yes, I do have duplicate records with 'BDF0A71D-A058-480A-80FB-42F3571DBCED'  in the ItemGuid column in TBLE_A
I may need re-design this to find other tables to accomplish this UPDATE

I didn't see you most recent post, thanks, will be testing this tomorrow

One last post on this as I am obviously not grasping the Primary Key issue adequately. This the actual query I am using that continues to give me the PK Constraint Errors

In summary, I am  attempting to UPDATE the column ResourceTargetGuiud in the ItemApplies to Table with GUID value 'BDF0A71D-A058-480A-80FB-42F3571DBCED'  on the condition the Enabled column from table ItemActive = False, Description column from the vItem view = 'This policy applies to Windows 2003' and to NOT update those records in the ResourceTargetGuid column of the ItemAppliesTo table where the value = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
I have a subquery in here as I don't see the proper method to JOIN 3 tables in an UPDATE.

Thanks again for any guidance on this.
I'm out.

Update ItemAppliesTo 
Set ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
from ItemActive
inner join  ItemAppliesTo 
on ItemActive.guid = ItemAppliesTo.itemGuid
where exists (select * from ItemActive iv, vItem i where iv.guid = i.guid
and iv.enabled = 'False' and i.description = 'This policy applies to Windows 2003')
and ItemAppliesTo.ResourceTargetGuid <> 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

Select allOpen in new window