How to find and then delete duplicates

[dpwsmw]

I have a table, that has duplicates in them, so Basically I would like to first find the duplicates with a script, and then delete all the duplicates

 

[manmaria]

Not enough information. Duplication on what? Whole record or a particular column. Give some minimal information about your table.

 

[dpwsmw]

Here is what I am trying to accomplish...
But I get an error



SELECT * FROM
dbo.AP1_001VendorMaster
WHERE EXISTS
(SELECT NULL FROM
B.[VendorNumber] = dbo.AP1_001VendorMaster.VendorNumber
AND B.[VendorName] = dbo.AP1_001VendorMaster.VendorName
GROUP BY
B.VendorNumber, B.VendorName
HAVING
dbo.AP1_001VendorMaster.VendorNumber < Max(B.VendorNumber)
)

**************************
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '='.

 

[manmaria]

In your From clause you have a '=' sign and it is expecting a table name.

So can I assume, you are trying to find out the duplicates on Vendor_Number and Vendor_Name. Or only on vendor Name.

Thanks

 

[dpwsmw]

I can do it on only VendorNumber

sELECT * FROM
dbo.AP1_001VendorMaster
WHERE EXISTS
(SELECT NULL FROM
B.VendorNumber = dbo.AP1_001VendorMaster.VendorNumber
GROUP BY
B.VendorNumber
HAVING
dbo.AP1_001VendorMaster.VendorNumber < Max(B.VendorNumber)
)

it still doesnt work, What am I doing wrong??

 

[manmaria]

You have dups on VendorNumber, is that right?

Just assume you are trying to find the dups on vendorNumber then query could be like this

Select * from dbo.AP1_001VendorMaster
where VendorNumber in
(Select VendorNumber from dbo.AP1_001VendorMaster
group by VendorNumber
Having count(*)>1)

 

[dpwsmw]

Ran that script, the problem with that is it returns all Rows in the Table, the table has duplicated itself once, so before I had 273 rows and now I have 546 rows, and every vendor is listed twice.

So, I have to find all the Duplicates and delete them. I ran your script and it returns 546 rows whcih means it sees all of them as Duplicates.

 

[manmaria]

If you run
SELECT COUNT(DISTINCT VendorNUmber) from dbo.AP1_001VendorMaster

should give you the non duplicated count then see how many are dups.

 

[manmaria]

Since you table is not big enough, try this to find the count on each vendor number

select VendorNumber,Count(*) from dbo.AP1_001VendorMaster
group by VendorNumber

 

[SQLDenis]

quick and dirty way

SELECT distinct * into dbo.AP1_001VendorMaster2
FROM
dbo.AP1_001VendorMaster

truncate table dbo.AP1_001VendorMaster

insert into dbo.AP1_001VendorMaster
select * from dbo.AP1_001VendorMaster2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

Try SELECT DISTINCT * FROM dbo.AP1_001VendorMaster

If that returns every row, then they aren't duplicates. SOMETHING is different in each row. Do you have a column that is an IDENTITY column? (IDENTITY autonumbers each entry). If so, then name each column in your SELECT and leave out that column.

-SQLBill

Posting advice: FAQ481-4875