How to write update query to check exist column by value 1 where it exist on table FeaturesvalueA or FeaturesvalueB only?

Transact-SQL
, ,
1

How to update check exist column by value 1 where partid exist on table FeaturesvalueA or FeaturesvalueB ?

I work on sql server 2012 I face issue I need to update column checkexist on table temp

where partid exist at least one time on table #FeaturesvalueA or table #FeaturesvalueB

But if partid exist on both tables then not update check exist by 1

i need only update check exist by 1 in case of exist on only one table from both

FeaturesvalueA or FeaturesvalueB

case of not update

IF partid exist on both tables #FeaturesvalueA and #FeaturesvalueB then no need to update column check exist by 1

IF partid not exist on both tables #FeaturesvalueA and #FeaturesvalueB then no need to update column check exist by 1

case of update

IF partid exist on only one tables from #FeaturesvalueA or #FeaturesvalueB then update check exist by 1

create table #temp
 (
 PartId int ,
 checkexist int,
 )
 insert into #temp(PartId,checkexist)
 values
 (555,0),
 (999,0),
 (1200,0),
 (1300,0),
 (1010,0),
 (1500,0)
    
    
 create table #FeaturesvalueA
 (
 PartId int,
 FeatureName nvarchar(50),
 FeatureValue  nvarchar(50),
 updatedStuffDiff nvarchar(500)
 )
 insert into #FeaturesvalueA(PartId,FeatureName,FeatureValue)
 values
 (555,'Temperature','5c'),
 (555,'resistance','10c'),
 (1200,'Temperature','20c'),
 (1200,'resistance','30c'),
 (1010,'cold','40c'),
 (1010,'air','7c')
    
    
 create table #FeaturesvalueB
 (
 PartId int,
 FeatureName nvarchar(50),
 FeatureValue  nvarchar(50),
 updatedStuffDiff nvarchar(500)
 )
 insert into #FeaturesvalueB(PartId,FeatureName,FeatureValue)
 values
 (555,'Temperature','5c'),
 (555,'resistance','10c'),
 (999,'Temperature','20c'),
 (1300,'resistance','30c'),
 (1010,'cold','40c'),
 (1010,'air','7c')

Expected result

 PartId    checkexist
 555            0
 999            1
 1200            1
 1300            1
 1010            0
 1500            0
2

this is just one way of doing it .. hope this helps !!!

select PartId  from #FeaturesvalueA WHERE PARTID NOT IN (select PartId   from #FeaturesvalueB ) 
union  
select PartId  from #FeaturesvalueB WHERE PARTID NOT IN (select PartId   from #FeaturesvalueA )

image

3

another way of doing it

select partid,count(*) from
	 (
		select distinct PartId  from #FeaturesvalueA 
		     union all  
		select distinct PartId  from #FeaturesvalueB 
      ) a
group by partid having count(*) = 1

image

4 
SELECT 
    COALESCE(A.PartId, B.PartId) AS PartId,
    CASE WHEN A.PartId IS NULL OR B.PartId IS NULL THEN 1 ELSE 0 END AS CheckExist
FROM (
    SELECT DISTINCT PartId
    FROM #FeaturesvalueA
) AS A
FULL OUTER JOIN (
    SELECT DISTINCT PartId
    FROM #FeaturesvalueB
) AS B ON A.PartId = B.PartId
ORDER BY PartId
5 
WITH CheckExist
AS
(
	SELECT PartId, checkexist
		,COALESCE(
			(
				SELECT TOP (1) 1
				FROM #FeaturesvalueA A
				WHERE A.PartId = T.PartId
			)
			, 0) AS InA
		,COALESCE(
			(
				SELECT TOP (1) 1
				FROM #FeaturesvalueB B
				WHERE B.PartId = T.PartId
			)
			, 0) AS InB
	FROM #temp T
)
UPDATE CheckExist
SET checkexist = 1
WHERE InA <> InB;
6

thank you for support