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)
Does anyone know how i can update the composite key?
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)
SQL:
--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