declare @t table
(
partid int,
maskid int,
chemicalid int,
status nvarchar(50)
)
insert into @t
(partid, maskid, chemicalid )
values
(100,1000,901),
(700,1700,909),
(400,1500,920)
declare @p table
(
partid int, maskid int
)
insert into @p
(partid, maskid )
values
(100,1000),
(200,1000),
(400,1500),
(600, 1700 ),
(700, 1700)
;with t as
(
select p1.partid from @p p1
inner join @p p2 on p1.maskid = p2.maskid and p1.partid != p2.partid
)
update t1
set [status] = 'conflicted'
from @t t1
inner join t t2
on t1.partid = t2.partid
select * from @t
I would look up self joins
sql - What is SELF JOIN and when would you use it? - Stack Overflow[
^]