t-sql - delete second value only

Question

i would like to run a sql statement that will delete ONLY the second value for example

delete from table1 where condition1

i want this statement to delete ONLY the second value

how can i accomplish this?

i would like to clarify. i have a field called field1 which is an autonumber and and it is a primary key and it increments. i would like to delete the record containing the greater number

How do you determine which value comes "first" and which value comes "second"? — Anon., Dec 08 '10 at 20:59
Is this meant to clean out duplicate entries? That's sort of what it looks like. — guildsbounty, Dec 08 '10 at 21:01

score 3 · Accepted Answer · answered Dec 08 '10 at 21:14

3

You could also employ the ROW_NUMBER() function of SQL server to number each row, and use this number to isolate just the second item for deletion, according to your own custom ordering in the inner query ( over (ORDER BY <myKey> asc) ). This provides a great deal of flexibility.

DELETE a FROM table1 
FROM table1 a 
JOIN (
        select ROW_NUMBER() over (ORDER BY <myKey> asc) as AutoNumber, <myKey> from table1
) b on a.<myKey> = b.<myKey>
WHERE condition1
AND b.AutoNumber = 2

answered Dec 08 '10 at 21:14

James

12,636
12
67
104

The self join isn't needed here. [You can delete from the CTE](http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows/3822833#3822833) – Martin Smith Dec 08 '10 at 21:42
@Martin: Good point - I always forget about CTE's, sometimes the syntax can be quite confusing though, this example is easier to digest IMHO. – James Dec 08 '10 at 21:48
Digest? You're executing it not eating it! The delete from the CTE without the self join is more efficient. – Martin Smith Dec 08 '10 at 21:59
The food for the mind is knowledge and more importantly understanding(something a CTE may negate)...so yes; `digest` is an apt figure of speech in this context. – James Dec 08 '10 at 23:32

Jeffrey Hantin · Answer 2 · 2010-12-08T22:48:56.723

1

Do you want to delete only the last duplicate, or all but the first?

For all but the first: (Edited to use CTE per @Martin's suggestion.)

with target as (select * from table1 where condition1)
delete from target goner
 where exists (select * from target keeper
                where keeper.field1 < goner.field1)

In other words, if there is another matching record with a lower field1, delete this record.

EDIT: To delete only the last:

with target as (select * from table1 where condition1)
delete from target goner
 where exists (select * from target keeper
                where keeper.field1 < goner.field1)
   and not exists (select * from target missing
                    where missing.field1 > goner.field1)

In other words, if there is another matching record with a lower field1, AND there is no matching record with a higher field1, then we have the highest duplicate, so nuke it.

edited Dec 08 '10 at 22:48

answered Dec 08 '10 at 21:06

Jeffrey Hantin

35,734
7
75
94

I don't think this works. SQL: `WITH TEMP as ( SELECT 1 as id,'A' as a,'Z' as b UNION SELECT 2,'A','Z' UNION SELECT 3,'B','Z' UNION SELECT 4,'B','Z' ) SELECT * from TEMP where id > 0 and exists (select * from TEMP other where other.id > 0 and other.id < TEMP.id)` – Abe Miessler Dec 08 '10 at 21:24
Notice that it selects 2,3 AND 4. – Abe Miessler Dec 08 '10 at 21:25
@Martin: Touché. It's possible that condition1 is a long, obnoxious expression, and we want to keep the code DRY. Shortens the query by a line too. – Jeffrey Hantin Dec 08 '10 at 22:41
You only want to nuke the last one, and leave 1, 2, and 3 alone? Okay. – Jeffrey Hantin Dec 08 '10 at 22:43
@Jeffrey - Touché Indeed! I didn't actually mean like that though. I meant [instead of any kind of explicit join](http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows/3822833#3822833) – Martin Smith Dec 08 '10 at 22:45
@Martin: Fair enough. I hadn't considered ROW_NUMBER() -- I was trying to approach it from a set-theoretic point of view, so I tend to miss techniques like that. – Jeffrey Hantin Dec 08 '10 at 22:52

Abe Miessler · Answer 3 · 2010-12-08T21:26:27.603

Try this:

DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(id) as id, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.id= KeepRows.id
WHERE
   KeepRows.RowId IS NULL

UPDATE

While this might not be as "pretty" as @Jeffrey's it works. From what I can tell, @Jeffrey's does not. See sql below (Delete replaced with SELECT * for demonstration):

WITH TEMP as
(
  SELECT 1 as id,'A' as a,'Z' as b
  UNION
  SELECT 2,'A','Z'
  UNION
  SELECT 3,'B','Z'
  UNION
  SELECT 4,'B','Z'
)

SELECT *
FROM TEMP
LEFT OUTER JOIN (
   SELECT MIN(id) as id, a, b
   FROM TEMP 
   GROUP BY a, b
) as KeepRows ON
   temp.id= KeepRows.id
WHERE
   KeepRows.id IS NULL

DELETE MyTable FROM MyTable ?? – JOE SKEET Dec 08 '10 at 21:05 — JOE SKEET, Dec 08 '10 at 21:05

score 0 · Answer 4 · answered Dec 08 '10 at 21:05

0

It's been a while (so my syntax my not quite be right), and this may not be the best solution, but the "academic" answer would be something like:

delete from table1 where condition1
and field1 = (select max(field1) from table1 where condition1)

answered Dec 08 '10 at 21:05

Anon.

58,739
8
81
86

t-sql - delete second value only

4 Answers4