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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

March 2, 2021 at 7:49 am

#3852109

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2069 More actions · Answer 1

Here's a simple way which avoids counting

with
a_cte as (select distinct PartId from #FeaturesvalueA),
b_cte as (select distinct PartId from #FeaturesvalueB),
x_cte as ((select * from a_cte except select * from b_cte)
           union all
          (select * from b_cte except select * from a_cte))
update t
set checkexist=1
from #temp t
     join x_cte x on t.PartId=x.PartId;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 2

Apparently you don't know that a table by definition, must have a key. Then you don't know how to design a table, even if it did have a key. You're doing what is called in EAV (entity – attribute – value) design , which is a classic beginners error. You've mixed attributes and metadata! We don't do that, and RDBMS. An update is a statement, and not a query. In order to have a key, you must have non-null columns. RDBMS does not use flags. If you're writing correct SQL, then you discover the current status of your data with predicates.

Nothing here is correct. START OVER.

Please post DDL and follow ANSI/ISO standards when asking for help.