Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Deleting Duplicate Records Expand / Collapse
Author
Message
Posted Wednesday, November 24, 2004 11:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 2:55 AM
Points: 142, Visits: 37
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/s


Kindest Regards,

Sureshkumar Ramakrishnan

Post #148008
Posted Thursday, November 25, 2004 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 09, 2005 10:22 AM
Points: 5, Visits: 1
Quite helpful
Post #148106
Posted Sunday, November 28, 2004 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 02, 2005 5:15 AM
Points: 1, Visits: 1
excellent & helpful script
Post #148290
Posted Monday, December 20, 2004 4:40 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 23, 2014 9:23 AM
Points: 63, Visits: 468

Clue: Anyone who begins by thinking "SQL Server doesn't have what Oracle has" is going to arrive at a solution that fails to make best use of SQL Server.  Simply add a column of datatype int and set the identity property to "Yes (not for replication)" and all the existing rows in the table will be given a row identifier.

>>>

create table test (name varchar(10))
insert into test values ('Kate')
insert into test values ('Kate')
insert into test values ('Sue')

-- Enterprise Manager is the easiest way to add a column, so add column ID using EM.  Datatype is int and property

create view testUniqueID as select name, min(Id) as minID
from test
group by name

delete test
from testuniqueID join test
on testuniqueID.name=test.name
where testuniqueID.minID < test.id

drop table test

drop view testUniqueID

>>>

Lesson: SQL Server does have a row identifier.  It stays out of the way unless you need it.  Anything Oracle does, SQL Server does more elegantly.




_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Post #151774
Posted Monday, December 20, 2004 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2008 9:50 AM
Points: 3, Visits: 15

Didn't find this very helpful.

For very large tables, the cursor approach would be dreadfully slow.

There are other techniques that exist, such as using duplicate indexes with IGNORE_DUP_KEY option that would be better for very large tables.

Cheers,




Post #151778
Posted Monday, December 20, 2004 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 2:55 AM
Points: 142, Visits: 37

Yeah I totally agree with SQL does things better than what Oracle does.My article is aimed at novice SQL developers who can find the script very convinent in situations wherein the data in a table is not very large and they can do data cleansing easily .

 

 

 

 




Kindest Regards,

Sureshkumar Ramakrishnan

Post #151782
Posted Monday, December 20, 2004 9:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 12, 2008 10:44 AM
Points: 376, Visits: 3

I thought the information was helpful as providing an example.  As to the performance, I try to look for ways to performing row oriented operations without cursors.  I would try to use new @table variable declaration instead for SQL 2000  and a temp table in 7.0.

Thanks for the the article

Post #151827
Posted Monday, December 20, 2004 11:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 12, 2007 2:10 PM
Points: 94, Visits: 1
Would it be just easier to save duplicated records in temp table where we have only 1 records for all duplicated records, then to delete all duplicated records in one shot from the original table and then insert records from the temp. table without any cursor?
Post #151861
Posted Monday, December 20, 2004 11:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 12, 2007 2:10 PM
Points: 94, Visits: 1

set nocount on
/* Script for Creation of Employee Table*/
CREATE TABLE [dbo].[#Employee] (
            [id] [int] NULL ,
            [name] [Varchar] (50)  NULL ,
            [salary] [Numeric](18, 2) NULL
  ON [PRIMARY]
GO
/*********************************************************/


/**********************************************************/
/* Script for Insertion of duplicate records to the Table */
Insert into #employee values (1,'Ram', 1000.00)
Insert into #employee values (1,'Ram', 1000.00)
Insert into #employee values (2,'Joe', 2000.00)
Insert into #employee values (2,'Joe', 1000.00)
Insert into #employee values (3,'Mary', 1000.00)
Insert into #employee values (4,'Julie', 5000.00)
Insert into #employee values (2,'Joe', 1000.00)
Insert into #employee values (1,'Ram', 1000.00)


select id,name,salary into #temp from #employee
group by id,name,salary
having count(*)>1


delete from e
from #employee e
  inner join #temp t on e.id=t.id and e.name=t.name and e.salary=t.salary

insert into #employee
select * from #temp


select * from #employee


drop table #Employee
drop table #temp

Post #151863
Posted Monday, December 20, 2004 4:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

The best way of dealing with duplicates is to avoid them in the first place!  Design your databases right and you don't have to worry about it.  There is no logically sound reason to allow duplicates.

If you find yourself in a situation (not of your own making, of course) where you have to deal with them, I have found this method helpful:

CREATE TABLE dupes (col1 int, col2 int)

GO
INSERT INTO dupes VALUES (1,1)
INSERT INTO dupes VALUES (1,1)
INSERT INTO dupes VALUES (1,1)
INSERT INTO dupes VALUES (2,2)
INSERT INTO dupes VALUES (2,2)
INSERT INTO dupes VALUES (2,2)
INSERT INTO dupes VALUES (2,2)

GO

CREATE TABLE nodupes (col1 int, col2 int)

GO

CREATE UNIQUE CLUSTERED INDEX ix
ON nodupes(col1, col2)
WITH IGNORE_DUP_KEY

GO

INSERT INTO nodupes
SELECT * FROM dupes

GO

SELECT * FROM nodupes

This gives you the safety of being able to do some "sanity checks" before actually modifying data, and it generally performs much better than cursor based methods.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #151903
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse