Update data to null when Id matches

  • Hi,

    I am trying to update the Type column to NULL when the following column value matches-id,age,catid and age. If they dont match then dont update the Type column to null.

    I tried below query but it update the whole column to NULL. it should not update the Type column to NULL for Id=3 because the age column has different values in it .

     

    DROP TABLE #mytable;

    CREATE TABLE #mytable(

    Id INTEGER NULL

    ,Empid INTEGER NULL

    ,CatId INTEGER NULL

    ,age NUMERIC(5,2) NULL

    ,Type VARCHAR(5) NULL

    );

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,101,10,3.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,101,11,4.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,101,12,5.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,12,5.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,11,4.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,10,3.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,103,11,4.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,103,10,3.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,103,12,5.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,105,66,1.50,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,105,67,60.00,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,106,67,60.00,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,106,67,1.50,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,55,77,1.50,'Type3');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,55,78,61.00,'Type3');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,66,77,62.00,'Type3');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,66,78,55.00,'Type3');

     

    I tried below-

    Update a

    SET Type=NULL

    from #mytable a JOIN #Mytable b

    ON (a.id=b.id and a.empid=b.empid and a.catid=b.catid)

    and a.age=b.age

     

    Please suggest.thanks

  • The sample data didn't contain any duplicates so I added a few.

    DROP TABLE if exists #mytable;
    go
    CREATE TABLE #mytable(

    Id INTEGER NULL

    ,Empid INTEGER NULL

    ,CatId INTEGER NULL

    ,age NUMERIC(5,2) NULL

    ,Type VARCHAR(5) NULL

    );

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,101,10,3.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,101,11,4.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,101,12,5.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,12,5.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,11,4.50,'Type1');
    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,11,4.50,'Type1');
    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,11,4.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,102,10,3.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,103,11,4.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,103,10,3.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (1,103,12,5.50,'Type1');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,105,66,1.50,'Type2');
    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,105,66,1.50,'Type2');
    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,105,66,1.50,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,105,67,60.00,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,106,67,60.00,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (2,106,67,1.50,'Type2');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,55,77,1.50,'Type3');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,55,78,61.00,'Type3');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,66,77,62.00,'Type3');

    INSERT INTO #mytable(Id,Empid,CatId,age,Type) VALUES (3,66,78,55.00,'Type3');


    select * from #mytable;

    with
    dupes_cte as(
    select
    Id, Empid, CatId, age, count(*) dupe_count
    from
    #mytable m
    group by
    Id, Empid, CatId, age
    having
    count(*)>1)
    update m
    set
    [Type]=null
    from
    #mytable m
    join
    dupes_cte dc on m.id=dc.id
    and m.empid=dc.empid
    and m.catid=dc.catid
    and m.age=dc.age;

    select * from #mytable;

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

  • Your query is perfectly doing it's job as it's written.

    If your intention is to identify & update the duplicate records basis certain set of columns then try to make use of COUNT with GROUP BY or RANKING FUNCTIONS with PARTITION BY. Once you will have the set of duplicate records then you can write another UPDATE statement which will JOIN with duplicate dataset. This can be a single query with CTE or multiple queries using Temporary Tables / Table Variables.

  • You may also consider to use not null on columns, and add a primary key and may be unique index, to prevent duplicates, in the first place.

  • i think it should work

    • This reply was modified 4 years, 10 months ago by  Papil.

Viewing 5 posts - 1 through 4 (of 4 total)

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