Subject: RE: STLWEBDEV: Here's a slightly tougher SQL question...
Date: Tue, 20 Mar 2001 12:35:45 -0600
Reply-To: stlwebdev@stlwebdev.org
--------------------------------------------------------------------------------
And to make that more general:
DELETE FROM table-name A
WHERE ROWNUM < (SELECT MAX(ROWNUM) FROM table-name B
WHERE A.NAME = B.NAME)
This will also go slowly on a big table. It has to step through every row
of the table, and *for each row* it has to do a second scan to find all the
rows where NAME matches. If NAME is indexed, the second scan will be pretty
fast.
Note that any of these plans will essentially save a random one of the rows
with Name="Matt"; if the different rows have different values in other
columns, you'll keep who-knows-which-one and lose the others.
ROWNUM is an Oracle extension to standard SQL. If you're using a database
other than Oracle, ROWNUM may not work. You should be able to replace it
with anything that is unique to each row, such as the primary key of the
table.
Nate Schroeder
nathan.e.schroeder@monsanto.com
-----Original Message-----
From: murthy@transition-tech.com [mailto:murthy@transition-tech.com]
Sent: Tuesday, March 20, 2001 9:00 AM
Cc: stlwebdev@stlwebdev.org; stlwebdev@stlwebdev.org
Subject: RE: STLWEBDEV: Here's a slightly tougher SQL question...
You can do it in one shot...
For example, if you have table EMPLOYEE (EMP_ID char(4)) which is holding 5
records with EMP_ID value
'1' and you want to get rid of 4 records and keep one
record..You can execute the following query to get
rid of four and retain one.
DELETE FROM EMPLOYEE
WHERE EMP_ID = '1'
AND ROWNUM < (SELECT MAX(ROWNUM) from EMPLOYEE
WHERE EMP_ID='1')
Hope this helps.
-Murthy
On Tue, 20 March 2001, "Shoemaker, Michael (N-MAXIM Group)" wrote:
> -> -----Original Message-----
> -> From: Matt Kaatman [mailto:mkaatman@thoughtprocess.net]
> -> Sent: Monday, March 19, 2001 6:08 PM
> -> To: stlwebdev@stlwebdev.org
> -> Subject: STLWEBDEV: Here's a slightly tougher SQL question...
> ->
> ->
> -> Is there a query I can do to delete all but one of any
> -> records that have
> -> duplicate entries for a particular field?
> ->
> -> So let's say I have 5 records with the value of "Matt" for
> -> the field Name, I
> -> want to delete 4 of those.
> ->
> -> I salute the person who can do this! I don't think it's
> -> possible to do in a
> -> single query. I have only figured out manual solutions that
> -> require a few
> -> steps. If it's possible to do it all in one query that would
> -> be awesome!
> ->
> -> Thanks!
> -> Matt Kaatman
> -> ThoughtProcess.net
> -> http://www.thoughtprocess.net
> ->
-- Anonymous, March 20, 2001