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

  • 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
  • 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

  • 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. 

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

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