Loading

How to write update query to check exist column by value 1 where it ex

How to write update query to check exist column by value 1 where it ex

ahmed elbarbary

5y1k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
try
update t
set t.checkexist = 1
from #temp as t
where
     case when exists(select 1 from #FeaturesvalueA as a where a.PartId = t.PartId)
         then 1
         else 0
     end +
     case when exists(select 1 from #FeaturesvalueB as b where b.PartId = t.PartId)
         then 1
         else 0
     end = 1
Pankajkumar Patel5y ago
Hi Ahmed Elbarbary,

Using except and common table expression (CTE) you can "write update query to check exist column by value 1 where it ex" as following:
;with CTE as (

    (select PartId from #FeaturesvalueA

    except

    select PartId from #FeaturesvalueB)

    union

    (select PartId from #FeaturesvalueB

    except

    select PartId from #FeaturesvalueA)

)

update #temp set checkexist = 1 FROM CTE

inner join #temp tmp on tmp.PartId = cte.PartId
For more detail you can refer below article:
The Complete Reference - Set Operations In MS SQL - UNION [ALL], INTERSECT, EXCEPT

https://www.c-sharpcorner.com/article/the-complete-reference-set-operations-in-ms-sql-union-all-intersect-excep/
Hope, this will help you!