CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Tom Hunter
Tom Hunter

Reputation: 5918

DELETE EXCEPT TOP 1

Is there any way to delete all the rows in a table except one (random) row, without specifying any column names in the DELETE statement?

I'm trying to do something like this:

CREATE TABLE [dbo].[DeleteExceptTop1]([Id] INT)
INSERT [dbo].[DeleteExceptTop1] SELECT 1
INSERT [dbo].[DeleteExceptTop1] SELECT 2
INSERT [dbo].[DeleteExceptTop1] SELECT 3

SELECT * FROM [dbo].[DeleteExceptTop1]

DELETE
FROM [dbo].[DeleteExceptTop1]
EXCEPT
SELECT TOP 1 * FROM [dbo].[DeleteExceptTop1]

SELECT * FROM [dbo].[DeleteExceptTop1]

The final SELECT should yield one row (could be any of the three).

Upvotes: 4

Views: 6532

Answers (6)

chris
chris

Reputation: 561

I know it has been answered but what about? 

DELETE 
FROM [dbo].[DeleteExceptTop1]
Where Id not in (
SELECT TOP 1 * FROM [dbo].[DeleteExceptTop1])

Upvotes: 1

David Manheim
David Manheim

Reputation: 2626

It seems like the simplest answer may be the best. The following should work:

Declare @count int
Set @count=(Select count(*) from DeleteExceptTop1)-1
Delete top (@count) from DeleteExceptTop1

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453308

;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT newid())) AS RN
FROM [dbo].[DeleteExceptTop1]
)
DELETE FROM CTE
WHERE RN > 1

Or similar to @abatishchev's answer but with more variety in the ordering and avoiding deprecated constructs.

DECLARE @C INT
SELECT @C = COUNT(*) - 1 
FROM [dbo].[DeleteExceptTop1]

IF @c > 0
BEGIN
WITH CTE AS
(
SELECT TOP(@C) *
FROM [dbo].[DeleteExceptTop1]
ORDER BY NEWID()
)
DELETE FROM CTE;
END

Or a final way that uses EXCEPT and assumes no duplicate rows and that all columns are of datatypes compatible with the EXCEPT operator

/*Materialise TOP 1 to ensure only evaluated once*/
SELECT TOP(1) * 
INTO #T
FROM [dbo].[DeleteExceptTop1]
ORDER BY NEWID()

;WITH CTE AS
(
SELECT *
FROM [dbo].[DeleteExceptTop1] T1
WHERE EXISTS(
             SELECT *
             FROM #T
             EXCEPT
             SELECT T1.*)
)
DELETE FROM CTE;

DROP TABLE #T

Upvotes: 6

abatishchev
abatishchev

Reputation: 100288

Try:

declare @c int
select @c = count(*) - 1 from [dbo].[DeleteExceptTop1]

IF @c > 0
BEGIN

set RowCount  @c

delete from [dbo].[DeleteExceptTop1]
END

Upvotes: 3

Matthew
Matthew

Reputation: 10444

No.
You need to use a column name (such as that of the primary key) to identify which rows you want to remove.

"random row" has no meaning in SQL except its data. If you want to delete everything except some row, you must differentiate that row from the others you with to DELETE

EXCEPT works by comparing the DISTINCT values in the row.

EDIT: If you can specify the primary key then this is a trivial matter. You can simply DELETE where the PK <> your "random" selection or NOT IN your "random" selection(s).

EDIT: Apparently I'm wrong about the need to specify any column name, you can do it using the assigned ROW_NUMBER.. But I'm not going to delete my answer because it references your use of EXCEPT which was discussed in the comments. You cannot do it without deriving some column name like that from ROW_NUMBER

Upvotes: 2

Wiz
Wiz

Reputation: 500

You could do something like this (SQL 2008)

DECLARE @Original TABLE ([Id] INT)
INSERT INTO @Original(ID) VALUES(1)
INSERT INTO @Original(ID) VALUES(2)
INSERT INTO @Original(ID) VALUES(3)

SELECT * FROM @Original;

WITH CTE AS
(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROW, ID FROM @Original)
DELETE @Original
FROM @Original O
INNER JOIN CTE ON O.ID = CTE.ROW
WHERE ROW > 1

SELECT * FROM @Original

Upvotes: 1

Related Questions