Delete TOP n Records in T-SQL
If you’re trying to delete the first n records in a table, here’s how to do it in SQL 2005:
use master
select *
into #temptable
from information_schema.tables
(12 row(s) affected)
select *
from #temptable
|
TABLE_CATALOG |
TABLE_SCHEMA |
TABLE_NAME |
TABLE_TYPE |
|
master |
dbo |
spt_fallback_db |
BASE TABLE |
|
master |
dbo |
spt_fallback_dev |
BASE TABLE |
|
master |
dbo |
spt_fallback_usg |
BASE TABLE |
|
master |
dbo |
lgnstats |
BASE TABLE |
|
master |
dbo |
pc_PluEventXref |
BASE TABLE |
|
master |
dbo |
LogonAudit |
BASE TABLE |
|
master |
dbo |
DBAobjectCNT |
BASE TABLE |
|
master |
dbo |
spt_monitor |
BASE TABLE |
|
master |
dbo |
spt_values |
BASE TABLE |
|
master |
dbo |
MSreplication_options |
BASE TABLE |
|
master |
dbo |
DatabasesBackupStrategy |
BASE TABLE |
|
master |
dbo |
DB_Size |
BASE TABLE |
delete top (5) from #temptable
(5 row(s) affected)
select *
from #temptable
|
TABLE_CATALOG |
TABLE_SCHEMA |
TABLE_NAME |
TABLE_TYPE |
|
master |
dbo |
LogonAudit |
BASE TABLE |
|
master |
dbo |
DBAobjectCNT |
BASE TABLE |
|
master |
dbo |
spt_monitor |
BASE TABLE |
|
master |
dbo |
spt_values |
BASE TABLE |
|
master |
dbo |
MSreplication_options |
BASE TABLE |
|
master |
dbo |
DatabasesBackupStrategy |
BASE TABLE |
|
master |
dbo |
DB_Size |
BASE TABLE |
In SQL 2000, you have to do it a little differently. I put an ORDER BY in the select statement so you could also see how to delete the ”BOTTOM” n records.
DELETE #temptable
FROM (
SELECT TOP 4 *
FROM #temptable
ORDER BY table_name desc) AS t1
WHERE #temptable.table_name=t1.table_name
(4 row(s) affected)
select *
from #temptable
|
TABLE_CATALOG |
TABLE_SCHEMA |
TABLE_NAME |
TABLE_TYPE |
|
master |
dbo |
DBAobjectCNT |
BASE TABLE |
|
master |
dbo |
DatabasesBackupStrategy |
BASE TABLE |
|
master |
dbo |
DB_Size |
BASE TABLE |
Recent Buzz