December 17, 2019 at 2:21 am
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
December 17, 2019 at 4:58 am
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
December 17, 2019 at 6:20 am
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.
December 17, 2019 at 7:16 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply