I need to find duplicate ids in a table and then search all those duplicates in five different tables to see which ones are used and which ones are not used.The idea is to delete the not used ids from the original table.
SELECT col1, col2, col3=count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
A.Venkatesan
Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
Marked as answer by Chen Yu - MSFT on Feb 27, 2012 07:55 AM
DECLARE @TAB TABLE( ID INT )
INSERT INTO @TAB
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 4
DECLARE @TAB1 TABLE( ID INT )
INSERT INTO @TAB1 SELECT 1 UNION ALL SELECT 3
DECLARE @TAB2 TABLE( ID INT )
INSERT INTO @TAB1 SELECT 4
SELECT ID
FROM @TAB
GROUP BY ID HAVING COUNT(1)>1
INTERSECT
SELECT ID FROM @TAB1
UNION
SELECT ID FROM @TAB2
--please check this demo
--replace @Table to your tableName
--use the query after replcing @table
Declare @Table Table
(
[name] varchar(50),
phone int,
address varchar(10),
data varchar(10)
)
insert into @Table
Select 'arun',1,'a','d'
Union All
Select 'ram',2,'b','a'
Union All
Select 'san',3,'c','t'
Union All
Select 'arun',1,'d','a'
Union All
Select 'ram',2,'e','aa'
--select duplicate records
;WITH CTE ([name],phone,address,data, DuplicateCount)
AS
(
SELECT [name],phone,address,data,
ROW_NUMBER() OVER(PARTITION BY [name],phone ORDER BY [name]) AS DuplicateCount
FROM @table
)
SELECT *
FROM CTE
WHERE DuplicateCount > 1
nissan
Participant
1065 Points
618 Posts
Duplicates in table
Feb 21, 2012 07:45 PM|LINK
Hello,
I need to find duplicate ids in a table and then search all those duplicates in five different tables to see which ones are used and which ones are not used.The idea is to delete the not used ids from the original table.
How should I do this without using cursors
venkatmca008
Participant
1810 Points
341 Posts
Re: Duplicates in table
Feb 21, 2012 09:35 PM|LINK
hi...to find duplicate in a table
Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
sandeepmitta...
Contributor
6957 Points
1082 Posts
Re: Duplicates in table
Feb 22, 2012 01:34 AM|LINK
Check this Example
Sandeep Mittal | My Blog - IT Developer Zone
D J
Contributor
5352 Points
941 Posts
Re: Duplicates in table
Feb 22, 2012 03:41 AM|LINK
To avoid cursor you can see this post
Delete Duplicate Records
For this you can make a check in TestCTE (as in above post) before deleting records.
yrb.yogi
Star
14460 Points
2402 Posts
Re: Duplicates in table
Feb 22, 2012 03:48 AM|LINK
.Net All About