How to delete TOP n results in SQL 2000?

Question

I know in SQL Server 2005 we could do something like:

DELETE TOP 10 FROM tbX where X = 1

What could be the query to do the same in SQL2K, considering that the primary key is composed by two FK columns?

Answer 1

DELETE
FROM tbX
WHERE id IN (SELECT TOP 10 id FROM tbX WHERE X = 1)

You would have to delete the foreign key records first.

Answer 2

when dealing with a composite key I would delete like this.

delete from tblX
from tblx as aa
join
(
  select top 10 * from tblX
) as bb
on
  aa.key1 = bb.key1
  and
  aa.key2 = bb.key2

Answer 3

hacky, but:

SET ROWCOUNT 10
DELETE FROM tbX where X = 1
SET ROWCOUNT 0

I wouldn't write this myself, though ;-p

(I'm off to wash my hands...)

Answer 4

This is not working SQL but something along the lines may work:

DELETE t
from tbx t 
join (
  select top 10 k1,k2 from tbx
) as t2 on t.k1 = t2.k1 and t.k2 = t2.k2

Note: If TOP does not work in the subquery just insert the stuff into a temp table and join.