how to delete duplicate record from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to delete duplicate record from a table

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-10-10 : 04:11:57

please suggest me the solution for how to delete duplicate record from a table.
regards
dasu.g

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-10 : 08:19:19

well you can use distinct to put the non duplicate records in a temp table.
then truncate the original table and insert data from temp table into the
original table.
you can also use union operator instead of distinct to do the same thing.

Go with the flow & have fun! Else fight the flow

dasu
Posting Yak Master

104 Posts

Posted - 2004-10-11 : 10:05:51

please send me the code which is helpful for me
thanks
regards
dasu.g

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 10:12:18

given the info u gave us this is the best i can do:

select distinct col1, col2, ... coln -- columns that have same data
into #tempTable
from MyTable

truncate table MyTable

insert into MyTable(col1, col2, ... coln)
select col1, col2, ... coln
from #temp

drop table #temp

Go with the flow & have fun! Else fight the flow

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-11 : 10:19:11

Spirit - Could you do this too?

SELECT DISTINCT *
into #tempTable
FROM myTable

TRUNCATE TABLE myTable

INSERT INTO myTable (col1, col2, ... coln)
SELECT *
FROM #tempTable

DROP TABLE #tempTable

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 10:22:56

yes you can.
INSERT INTO myTable (col1, col2, ... coln)
SELECT * -- change this to col list
FROM #tempTable

Go with the flow & have fun! Else fight the flow

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-11 : 10:28:38

On the insert ... you would need to list the column names? I thought that by specifying the column names with the INSERT INTO, you didnt need to list them again in the SELECT statement as long as they were in the same order. I am trying to get a little more advanced with what I am doing with SQL Server and I just want to understand fully.