September 24, 2015 at 6:41 am
i have one table having three columns.This table contains lot of repeated records.
i want to delete this records .
In this below example i want to delete all the records
which columns id and no columns contains same values.
id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1
first three records the columns id and no contains same value.
i want to delete this three records.
but in last record for id =35 and no column =45623.it is not repeated so it should not be
deleted.
September 24, 2015 at 6:44 am
jkramprakash (9/24/2015)
i have one table having three columns.This table contains lot of repeated records.i want to delete this records .
In this below example i want to delete all the records
which columns id and no columns contains same values.
id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1
first three records the columns id and no contains same value.
i want to delete this three records.
but in last record for id =35 and no column =45623.it is not repeated so it should not be
deleted.
This is TSQL Level 101, and there are numerous ways of going about it. What have you tried so far?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 24, 2015 at 9:13 am
i tried below query
DELETE FROM [TableName]
WHERE id IN(
SELECT id
FROM [TableName]
GROUP BY id
HAVING COUNT(id) > 1)
but it is checking only ID column not NO column.I want to check both columns.
September 24, 2015 at 11:10 am
Maybe something like,
DELETE FROM [TableName] WHERE id IN(
SELECT id FROM [TableName] GROUP BY id HAVING COUNT(id) > 1
UNION
SELECT id FROM [TableName] WHERE [no] IN (SELECT [no] FROM [TableName] GROUP BY [no] HAVING COUNT([no]) > 1)
)
September 24, 2015 at 11:49 am
jkramprakash (9/24/2015)
. . . first three records the columns id and no contains same value.i want to delete this three records.
but in last record for id =35 and no column =45623.it is not repeated so it should not be
deleted.
Try this:
declare @t table
(
id int,
num int,
seq int
)
insert @t values
(35, 35432, 1),
(35, 35432, 2),
(35, 35432, 3),
(36, 35432, 1),
(35, 45623, 1)
;
with cte as
(
select id, num, count(*) dup_cnt
from @t
group by id, num
)
delete t
--select *
from @t t
inner join cte c on c.id = t.id and c.num = t.num
where c.dup_cnt > 1
select * from @t
If you don't like the cte, you can move it into the inner join.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 24, 2015 at 1:06 pm
Or avoid the JOINs.
WITH CTE AS
(
SELECT id, num, COUNT(*) OVER(PARTITION BY id, num) dup_cnt
FROM @t
)
DELETE c
--SELECT *
FROM cte c
WHERE c.dup_cnt > 1;
September 24, 2015 at 2:03 pm
Luis Cazares (9/24/2015)
Or avoid the JOINs.
😎
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 25, 2015 at 1:01 am
Thank you
September 25, 2015 at 7:52 am
jkramprakash (9/25/2015)
Thank you
You're welcome.
However, do you understand the solutions?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply