Update Composite Key 1

[Ringers]

Hi All,

I need to update Table A which has a composite key from Table B and Table C.

Update Table A Group_id to Table B ID

Where the user_id in Table A is '1234'

and company_id in B is the same as the users company_id in Table C

and the Table B Name is like 'non user'

Table A(dual PK)
PK groupid(INT)
PK userid(INT)

TABLE B
PK ID(INT)
Name(varchar)
desc(varchar)
companyid(int)

TABLE C(no PK)
userid(int)
level(varchar)
dept(varchar)
div(varchar)
companyid(int)

--This Returns the exact id from table B i need
select b.id
from dbo.user_groups b
inner join dbo.userproperties c
on c.company_id = b.company_id
where c.User_id = '1311' and b.Name = 'Non User'
-- I then try to update
UPDATE a
 SET a.group_id = b.ID,
	a.user_id = c.User_id
FROM user_group_members a
inner join userproperties c
on a.user_id = c.User_id
inner join dbo.user_groups b
on c.company_id = b.company_id
where c.User_id = '1311'and b.Name = 'Non User'
-- But I error Violation of PRIMARY KEY constraint 'PK_group_membership'. Cannot insert duplicate key in object table A

Does anyone know how i can update the composite key?

 

[djj55]

What about a LEFT JOIN to User_Group_Members and a where the key value is NULL

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Ringers]

Thanks for the reply djj. can you show me what you mean, currently I am not joining to the user_group_members table.

 

[Ringers]

OK I think I have worked a part of the issue. Well it solves the primary key error.

select a.group_id, a.user_id, b.Description,b.company_id, c.gen_department, c.company_id
from user_group_members a
inner join user_groups b
on a.group_id = b.ID
inner join userproperties c
on a.user_id = c.User_id and b.company_id = c.company_id
where a.user_id = 1311
order by group_id

Returns 51 Rows.
Include a join between Table B & Table C on the user_id & Company_id, this correctly identifies the correct group_id in table A to update.

UPDATE a
	SET a.group_id = b.ID
FROM user_group_members a
inner join user_groups b
on a.group_id = b.ID
inner join userproperties c
on a.user_id = c.User_id and b.company_id = c.company_id
where a.user_id = 1311 and b.Name = 'Non User'

But this only updates 2 rows, there correct but what the other 49 rows?

 

[Ringers]

UPDATE	a
	SET	a.group_id = b1.id

from @user_group_members a
inner join @user_groups b
on a.group_id = b.ID
inner join @userproperties c
on a.user_id = c.User_id and b.company_id = c.company_id
inner join	@user_groups b1
on			b1.company_id	=	b.company_id
and			b1.description	=	'Non User'
and			b1.id			<>	b.id
where a.user_id = 1311

This is the answer