Update data to null when Id matches

Question

Update data to null when Id matches

Papil

SSCommitted

Points: 1983
More actions
December 17, 2019 at 2:21 am

#3708326

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

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

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 2

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.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 3

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.

Papil SSCommitted Points: 1983 More actions · Answer 4

i think it should work

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