Search code examples
sqlsql-serverdelete-row

SQL Delete TOP statement

my table (sql sever 2000) contains large amount of rows, when i delete row, it extremely slow.

i saw some post/forum that suggest to use a WHILE loop function to delete record by batch.

my sql is:

DELETE FROM Tprs_master where Fincmonth='201109';

I had rewrite to:

DELETE FROM(SELECT TOP 100 FROM tprs_master) AS t1 WHERE t1.Fincmonth='201109'

but it doesn't work.

how can I rewrite the above sql to DELETE TOP style ? Thanks

Solution

  • The MSDN DELETE page gives examples and syntax

    DELETE TOP (100) FROM Tprs_master where Fincmonth='201109';

    Note: SQL Server 2005+ only

    Also see for more examples Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)

    Edit: OP has SQL Server 2000

    SET ROWCOUNT 100
DELETE FROM Tprs_master where Fincmonth='201109';
SET ROWCOUNT 0