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

Author

Topic

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-05 : 04:16:32

Hai there... I'm facing a problem when i want to delete.. I have a data that looks like this...

SERIAL PIN
100001 88
100001 77
100002 66
100002 99
900001 88
900002 66

So i want to delete the row of data based on the duplicate pin.. not the duplicate serial number.. So the result will look like this..

SERIAL PIN
100001 77
100002 99
900001 88
900002 66

how am i to do this?

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 04:20:45

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Deleting+Duplicate+Records

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:20:51

There are many examples found in the FAQ
found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:21:01

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

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-05 : 04:23:13

Which record do you want to keep?
Lowest SERIAL?

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-05 : 04:28:13

I know.. i already try it.. this is my query..

DELETE SERIALTABLE
FROM SERIALTABLE
JOIN
(select [PIN], max([SERIAL]) AS SerialID
from SERIALTABLE
group by [PIN]) AS G
ON G.[PIN] = shlserialkeys.[PIN]
WHERE SERIALTABLE.[SERIAL] < G.[SerialID]
AND SERIALTABLE.[PIN] = G.[PIN]

but this doesn't work.. :( It only delete a few data...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:34:28

Oh, I think we have misinterpreted this request. It has nothing to with duplicates.
See this solution

DECLARE	@Sample TABLE (Serial INT, Pin INT)

INSERT	@Sample
SELECT	100001, 88 UNION ALL
SELECT	100001, 77 UNION ALL
SELECT	100002, 66 UNION ALL
SELECT	100002, 99 UNION ALL
SELECT	900001, 88 UNION ALL
SELECT	900002, 66

select * from @sample order by pin, serial

DELETE		x
FROM		@Sample AS x
INNER JOIN	(
			SELECT		Pin,
					MAX(Serial) AS theSerial
			FROM		@Sample
			GROUP BY	Pin
		) AS p ON p.Pin = x.Pin AND p.theSerial > x.Serial

select * from @sample order by pin, serial

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:35:26

quote:
Originally posted by massspectrometry

I know.. i already try it.. this is my query..

DELETE SERIALTABLE
FROM SERIALTABLE
JOIN
(select [PIN], max([SERIAL]) AS SerialID
from SERIALTABLE
group by [PIN]) AS G
~~ON G.[PIN] = shlserialkeys.[PIN]~~
~~WHERE~~ ON SERIALTABLE.[SERIAL] < G.[SerialID]
AND SERIALTABLE.[PIN] = G.[PIN]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 04:41:40

[code]DECLARE @SERIALTABLE TABLE
(
SERIAL int,
PIN int
)

INSERT INTO @SERIALTABLE
SELECT 100001, 88 UNION ALL
SELECT 100001, 77 UNION ALL
SELECT 100002, 66 UNION ALL
SELECT 100002, 99 UNION ALL
SELECT 900001, 88 UNION ALL
SELECT 900002, 66

DELETE d
FROM @SERIALTABLE d
INNER JOIN
(
SELECT PIN, SERIAL = MAX(SERIAL)
FROM @SERIALTABLE
GROUP BY PIN
) k
ON d.PIN = k.PIN
AND d.SERIAL <> k.SERIAL

SELECT *
FROM @SERIALTABLE

/*
SERIAL PIN
----------- -----------
100001 77
100002 99
900001 88
900002 66
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 04:43:15

DELETE	d
FROM	@SERIALTABLE d
	left JOIN
	(
		SELECT	PIN, SERIAL = MAX(SERIAL)
		FROM	@SERIALTABLE
		GROUP BY PIN
	) k	
	ON	d.PIN		= k.PIN
	AND	d.SERIAL	= k.SERIAL
WHERE	k.PIN	IS NULL

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 04:44:37

D A M N ! ! !

where all that comes from ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:45:52

For SQL Server 2005

DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY Pin ORDER BY Serial DESC) AS RecID
		FROM	@Sample
	) AS f
WHERE	RecID > 1

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

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 05:17:04

"Which record do you want to keep?
Lowest SERIAL?"

Note that the OPs example appears to show ramdon SERIAL kept

Kristen

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-05 : 05:31:54

erm...(pls don't be angry) may i know where to fing ROW_NUMBER()? Is it in script library?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 05:44:21

No, it is for SQL Server 2005, as I mentioned.
If you are running on SQL Server 2000, you can't use it.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 06:40:36

Also Refer Nigel's more methods
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-05 : 10:48:35

More work if data like these for table @Sample
INSERT @Sample
SELECT 100001, 88 UNION ALL
SELECT 100001, 77 UNION ALL
SELECT 100002, 66 UNION ALL
SELECT 100002, 66 UNION ALL
SELECT 900002, 66 UNION ALL
SELECT 100002, 99 UNION ALL
SELECT 900001, 88

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 10:57:11

Still work, because of "SELECT 900002, 66 UNION ALL".
But if you delete this sample data, output is

and NOW you have the cases with duplicates! See link posted by Kristen.

PS: If using SQL Server 2005, my suggestion above would have taken care of this too.

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

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-05 : 21:17:40

Erm.. can i add a condition?.. like.. in one table there are millions of data.. but i only want to delete 1000 data with duplicate pin... and the duplicate pin is at the bottom.. Example

SERIAL PIN
100001 88 <-- duplicate pin start from here
100001 77
100002 66
100002 99
|
|
(millions of data) <-- i don't want to delete anything from here..
|
|
900001 88 <-- starting from here to check the duplicate pin
900002 66

is it possible to add a condition like this? :?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 02:57:27

If you have identity column (without any gap) , then you could use

Delete from ...
(
select columns from table where indentity_col>1000000)
...

Madhivanan

Failing to plan is Planning to fail

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-09-06 : 04:03:56

how?.. is it a range?

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources