Duplicate Record Deletion - SQL Server Forums

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

SQL Server Development (2000)

Duplicate Record Deletion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-12 : 23:05:11

Riaz Ahmed Farooqui writes "How Can I Delete Duplicate Record In One Table through Query.
For Example Table is

Customer Code Customer Name
1 Riaz
1 Riaz
2 Aleem
2 Aleem
3 Kaleem
3 Kaleem
4 Naeem
4 Naeem"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-12 : 23:05:11

A quick search of the site (which we always suggest you do before posting a question) turns up this article (http://www.sqlteam.com/item.asp?ItemID=3331) which is titled "Deleting Duplicate Records."

jhun_garma
Starting Member

15 Posts

Posted - 2002-03-12 : 23:42:05

try this

select * from table
union
select * from table where ( a conditon that gives 0 records )

the union statements join two tables then delete all duplicates

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-13 : 00:12:08

Riaz Ahmed

You might also be looking for
select distinct CustomerCode, CustomerName from Customer

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 03/13/2002 00:13:19

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-13 : 00:12:56

Riaz,

Try this for a quick a dirty solution....


set rowcount 1
declare @Code int
Select 1
while @@RowCOunt > 0
begin
	Delete Customer from Customer C where exists
	(Select Code from Customer
	where Code = C.Code
	group by Code
	having Count(*) > 1)
end

Nice one jhun_garma..

Except you don't need the where clause on the lower union


Select * from Table
union
Select * from Table

It doesn't actual "delete" rows.. just "hides" them from the set

DavidM

Tomorrow is the same day as Today was the day before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources