find duplicate records - 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

Transact-SQL (2000)

find duplicate records

Author

Topic

RK88
Starting Member

5 Posts

Posted - 2005-03-24 : 13:15:51

I have a table, called TABLE, with millions of rows with 10+ fields. Here are the 3 fields that I'm looking at.

ITEM_ID -- Integer and NOT unique
UNITS -- Integer
SP_CODE -- varchar

Here is the problem:
Some of the records are duplicates and need to be removed.

I need to find the ITEM_ID where SP_CODE exists and those same ITEM_ID exists without a SP_CODE. If their UNITS are equal, then those are the duplicates.

I can find those 2 groups separately. But how can I compare them and return only the duplicates? Can anyone help? Thanks :-)

RK88

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-24 : 13:29:29

I Hope you have a unique field in this table.
I am assuming you want to drop the record without the without a SP_CODE.

Deleat from table

where SP_CODE is null and ITEM_ID in(
Select ITEM_ID
from table
where Count(*) > 1
Group by ITEM_ID,UNITS)

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-24 : 13:30:44

SELECT * FROM TABLE a JOIN TABLE b ON ITEM_ID = ITEM_ID WHERE a.SP_CODE IS NOT NULL AND b.sp_CODE IS NULL AND a.UNITS = b.UNITS

????????

Brett

8-)

RK88
Starting Member

5 Posts

Posted - 2005-03-24 : 13:40:42

No, I don't want to delete all the rows w/o the SP_CODE because there are some 'good' records that don't have SP_CODE.

All I want is find the rows that have the same ITEM_ID, UNITS but SP_CODE is NULL vs NOT NULL.

THANK :-)

RK88
Starting Member

5 Posts

Posted - 2005-03-24 : 14:43:00

X002548,
I'll not sure if yours is correct. It returned too many rows w/ some ITEM_ID returns 80 or 90 rows. I'm thinking more like something like this:

SELECT *
from TABLE t1
where sp_code is null
and exists (select item_id, units
from TABLE t2
where t1.item_id = t2.item_id
and t1.units = t2.units
and t2.sp_code is not null)

Do you think this will find the duplicates where ITEM_ID and UNITS are the same but have different SP_CODE?

RK88

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources