Delete Duplicate problem - 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 problem

Author

Topic

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-24 : 23:33:12

Hai.. I know there are already lots of topic of this subject and i already try most of it including this link :
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83177&SearchTerms=delete,duplicate,row [/url]
& it's still didn't give me the result i want..

like most of the post, the data i have look something like this

Serial I_D Date_Entry
111111 AAA 2007-07-07
111111 AAA 2007-07-14
222222 BBB 2007-07-07
222222 BBB 2007-07-14

i just want to keep one entry and eliminate any duplicate row. I try this query,

Delete t1 From SerialTable t1
Where
Date_Entry =
(
Select Min(Date_Entry) From SerialTable t2 Where t1.[Serial]= t2.[Serial]
)
And Exists
(
Select [Serial] From SerialTable t2 Where t1.[Serial]= t2.[Serial]
Group by Serial
Having Count(1) >1
)

And I_D = 'AAA' and
Serial >= '111111' and
Serial <= '999999'

but it didn't delete any data, it gives

(0 row(s) affected)

Please tell me what did i do wrong?

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 01:44:44

Works fine for me:


CREATE TABLE #SerialTable
(
	Serial	varchar(10) NOT NULL,
	I_D	varchar(10) NOT NULL,
	Date_Entry datetime NOT NULL
)

INSERT INTO #SerialTable
SELECT '111111', 'AAA', '20070707' UNION ALL
SELECT '111111', 'AAA', '20070714' UNION ALL
SELECT '222222', 'BBB', '20070707' UNION ALL
SELECT '222222', 'BBB', '20070714'

SELECT * FROM #SerialTable

Delete t1 
From #SerialTable t1 
Where 
Date_Entry = 
(
Select Min(Date_Entry) From #SerialTable t2 Where t1.[Serial]= t2.[Serial]
)
And Exists 
(
Select [Serial] From #SerialTable t2 Where t1.[Serial]= t2.[Serial]
Group by Serial
Having Count(1) >1 
)

And I_D = 'AAA' and 
Serial >= '111111' and
Serial <= '999999'

SELECT * FROM #SerialTable

DROP TABLE #SerialTable


(4 row(s) affected)

Serial     I_D        Date_Entry                                             
---------- ---------- ------------------------------------------------------ 
111111     AAA        2007-07-07 00:00:00.000
111111     AAA        2007-07-14 00:00:00.000
222222     BBB        2007-07-07 00:00:00.000
222222     BBB        2007-07-14 00:00:00.000

(4 row(s) affected)


(1 row(s) affected)

Serial     I_D        Date_Entry                                             
---------- ---------- ------------------------------------------------------ 
111111     AAA        2007-07-14 00:00:00.000
222222     BBB        2007-07-07 00:00:00.000
222222     BBB        2007-07-14 00:00:00.000

(3 row(s) affected)

Kristen

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-25 : 02:06:04

But it didn't work for me :(

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 03:09:30

What does


SELECT *
FROM SerialTable
WHERE I_D = 'AAA' and 
Serial >= '111111' and
Serial <= '999999'

show you (after you have done the delete)?

is the code you posted above the EXACT code that you are using?

Kristen

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-25 : 03:28:17

It's not the exact code.. But when i use the select query, it gives 30k total data.. I only want 15k data because another 15k is the duplicate data.. My query looks like this :-

/*Within range and with ID*/
select * from shlserialkeys
where srlcustomerid = 'ROTTA' and
srlserial >= '1260001' and
srlserial <= '1275000'

/*Delete duplicate*/
Delete t1 From shlserialkeys t1
Where
srllastretrydate =
(
Select Min(srllastretrydate) From shlserialkeys t2 Where t1.[srlserial]= t2.[srlserial]
)
And Exists
(
Select [srlserial] From shlserialkeys t2 Where t1.[srlserial ]= t2.[srlserial]
Group by srlserial
Having Count(1) >1
)

And srlcustomerid = 'ROTTA' or srlcustomerid = 'Rotta' and
srlserial >= '1260001' and
srlserial <= '1275000'

and the actual data is

IDNO. SERIAL DATE
ROTTA 1260001 2007-07-30 00:00:00.000
Rotta 1260001 2007-07-17 00:00:00.000
ROTTA 1260002 2007-07-30 00:00:00.000
Rotta 1260002 2007-07-17 00:00:00.000

It's the same isn't it? :(

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 05:12:30

Is it a case sensitive database, or the srlcustomerid column has a case sensitive collation?

Kristen

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-25 : 16:46:36

What does this do?


Delete shlserialkeys
From shlserialkeys 
join (select Min(srllastretrydate) as deleteDate, [srlserial] 
	From shlserialkeys
	Group by srlserial
	Having Count(1) >1) as Derived1
on shlserialkeys.srllastretrydate = Derived1.deleteDate
and shlserialkeys.srlserial = Derived1srlserial

Where  (shlserialkeys.srlcustomerid = 'ROTTA' or shlserialkeys.srlcustomerid = 'Rotta') 
	and shlserialkeys.srlserial >= '1260001' 
	and shlserialkeys.srlserial <= '1275000'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 17:04:13

quote:
Originally posted by massspectrometry

...
And srlcustomerid = 'ROTTA' or srlcustomerid = 'Rotta' and
srlserial >= '1260001' and
srlserial <= '1275000'

It's the same isn't it?

You have to beware of operator presedence!

And (srlcustomerid = 'ROTTA' or srlcustomerid = 'Rotta') and
srlserial >= '1260001' and
srlserial <= '1275000'

And srlcustomerid IN ('ROTTA', 'Rotta')
and srlserial BETWEEN '1260001' and '1275000'

E 12°55'05.25"
N 56°04'39.16"

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-27 : 17:03:58

I assume you'd like to delete duplicated rows with smaller date_entry

so, the following is one solution and it works fine.

Delete t
from #SerialTable t
inner join
(
select s2.serial, s2.I_D, s2.date_entry from #SerialTable s1
inner join #SerialTable s2 on s2.serial=s1.serial and s2.I_D=s1.I_D
where s2.Date_Entry < s1.Date_Entry
) w
on w.serial=t.serial and w.I_D=t.I_D and w.Date_Entry = t.Date_Entry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources