Delete 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

SQL Server Development (2000)

Delete Duplicate Records

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-06 : 10:26:49

SQL 2005

Have a table orderrebate with the following fields.

ord_type
ord_no
item_no
lin_seq_no
cd_tp

I can have cd_tp 1 and cd_tp 3

I want to be able to delete the cd_tp 3 when the other 4 fields are the same.

IN the example below I want to delete the records with cd_tp 3

ord_type ord_no item_no lin_seq_no cd_tp
12 22 BRAC 5 1
12 22 BRAC 5 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-06 : 10:40:50

SELECT ord_type, ord_no, item_no,
from orderrebate
group by ord_type, ord_no, item_no
having count(*) > 1
and having sum(case when cd_tp = 3 then 1 else 0 end) > 0

E 12°55'05.63"
N 56°04'39.26"

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-06 : 11:04:54

I also found this. Thanks

DELETE t
FROM YourTable t
INNER JOIN
(SELECT ord_type ord_no item_no line_seq_no
FROM YourTable
WHERE cd_tp IN (1,3)
GROUP BY ord_type ord_no item_no line_seq_no
HAVING COUNT(DISTINCT cd_tp)=2)tmp
ON t.ord_type = tmp.ord_type
AND t.ord_no = tmp.ord_no
AND t.item_no = tmp.item_no
AND t.line_seq_no = tmp.line_seq_no
WHERE t.cd_tp=3

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-06 : 11:48:23

now how would I do it if all fields were identical?

Now is just want to delete duplicate records where all fields are hte same

table: orderrebate
fields; ord_type
ord_no,
item_no,
line_seq_no,
cd_tp

ma.voice
Starting Member

12 Posts

Posted - 2008-09-06 : 13:54:52

Try following code:

CREATE TABLE #emp
(
empno int,
ename varchar(100),
deptno int
)
GO

INSERT INTO #emp values (1,'A',9)
INSERT INTO #emp values (1,'A',9)
INSERT INTO #emp values (1,'A',9)
INSERT INTO #emp values (1,'A',9)
GO

SELECT * FROM #emp
GO

DELETE T
FROM
(
SELECT TOP 3 * FROM #emp
) as T

SELECT * FROM #emp
GO

DROP TABLE #emp
GO

Hopefully this will solve your problem

Cheers.

Silent Voice
Bill Gates, MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-06 : 15:05:14

Oh, you have posted in the wrong forum.
You have SQL Server 2005?

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARITION BY ord_type, ord_no, item_no, lin_seq_no ORDER BY cd_tp) AS RecID
) AS f
WHERE RecID > 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources