In SQL Server 2000, how to delete the specified rows in a table that does not have a primary key?

Let's say we have a table with some data in it.

IF OBJECT_ID('dbo.table1') IS NOT NULL
BEGIN
    DROP TABLE dbo.table1;
END
CREATE TABLE table1 ( DATA INT );

---------------------------------------------------------------------
-- Generating testing data
---------------------------------------------------------------------
INSERT INTO dbo.table1(data)
SELECT 100
UNION ALL
SELECT 200
UNION ALL
SELECT NULL
UNION ALL
SELECT 400
UNION ALL
SELECT 400
UNION ALL
SELECT 500
UNION ALL
SELECT NULL;

How t开发者_StackOverflowo delete the 2nd, 5th, 6th records in the table? The order is defined by the following query.

SELECT  data
FROM    dbo.table1
ORDER BY data DESC;

Note, this is in SQL Server 2000 environment.

Thanks.

---

In short, you need something in the table to indicate sequence. The "2nd row" is a non-sequitur when there is nothing that enforces sequence. However, a possible solution might be (toy example => toy solution):

If object_id('tempdb..#NumberedData') Is Not Null
    Drop Table #NumberedData

Create Table #NumberedData
(
Id int not null identity(1,1) primary key clustered
, data int null
)

Insert #NumberedData( data )
SELECT 100
UNION ALL SELECT 200
UNION ALL SELECT NULL
UNION ALL SELECT 400
UNION ALL SELECT 400
UNION ALL SELECT 500
UNION ALL SELECT NULL

Begin Tran

Delete table1

Insert table1( data )
Select data
From #NumberedData
Where Id Not In(2,5,6)

If @@Error <> 0 
    Commit Tran
Else 
    Rollback Tran

Obviously, this type of solution is not guaranteed to work exactly as you want but the concept is the best you will get. In essence, you stuff your rows into a table with an identity column and use that to identify the rows to remove. Removing the rows entails emptying the original table and re-populating with only the rows you want. Without a unique key of some kind, there just is no clean way of handling this problem.

---

As you are probably aware you can do this in later versions using row_number very straightforwardly.

delete t from 
(select ROW_NUMBER() over (order by data) r from table1) t   
where r in (2,5,6)

Even without that it is possible to use the undocumented %%LOCKRES%% function to differentiate between 2 identical rows

SELECT data,%%LOCKRES%%
FROM dbo.table1`

I don't think that's available in SQL Server 2000 though.

In SQL Sets don't have order but cursors do so you could use something like the below. NB: I was expecting to be able to use DELETE ... WHERE CURRENT OF but that relies on a PK so the code to delete a row is not as simple as I was hoping for.

In the event that the data to be deleted is a duplicate then there is no guarantee that it will delete the same row as CURRENT OF would have. However in this eventuality the ordering of the tied rows is arbitrary anyway so whichever row is deleted could equally well have been given that row number in the cursor ordering.

DECLARE @RowsToDelete TABLE
(
rowidx INT PRIMARY KEY
)
INSERT INTO @RowsToDelete SELECT 2 UNION SELECT 5 UNION SELECT 6

DECLARE @PrevRowIdx int
DECLARE @CurrentRowIdx int
DECLARE @Offset int
SET @CurrentRowIdx = 1
DECLARE @data int

DECLARE ordered_cursor  SCROLL CURSOR FOR
SELECT data
FROM dbo.table1
ORDER BY data

OPEN ordered_cursor

FETCH NEXT FROM ordered_cursor INTO @data

WHILE EXISTS(SELECT * FROM @RowsToDelete)
BEGIN
SET @PrevRowIdx = @CurrentRowIdx
SET @CurrentRowIdx = (SELECT TOP 1 rowidx FROM @RowsToDelete ORDER BY rowidx)
SET @Offset = @CurrentRowIdx - @PrevRowIdx
DELETE FROM @RowsToDelete WHERE rowidx = @CurrentRowIdx

FETCH RELATIVE @Offset FROM ordered_cursor INTO @data

/*Can't use DELETE ... WHERE CURRENT OF as here that requires a PK*/
SET ROWCOUNT 1
DELETE FROM dbo.table1 WHERE (data=@data OR data IS NULL OR @data IS NULL)
SET ROWCOUNT 0

END

CLOSE ordered_cursor
DEALLOCATE ordered_cursor

---

To perform any action on a set of rows (such as deleting them), you need to know what identifies those rows.

So, you have to come up with criteria that identifies the rows you want to delete.

Providing a toy example, like the one above, is not particularly useful.

---

You plan ahead and if you anticipate this is possible you add a surrogate key column or some such.

In general you make sure you don't create tables without PK's.

It's like asking "Say I don't look both directions before crossing the road and I step in front of a bus."