I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I instead of just deleting the top 1000 rows it deleted all the rows in the table.
Here is the code:
delete from [mytab]
select top 1000
a1,a2,a3
from [mytab]
On the menu bar visit Edit -> Preferences . Expand SQL Editor . Select SQL Execution . In the SELECT Query Results section, you can either uncheck Limit Rows or increase/decrease the Limit Rows Count.
In SQL Server, DELETE TOP statement is used to delete the records from a table and limit the number of records deleted regarding a fixed value or percentage. Syntax: DELETE TOP (top_value) [ PERCENT ] FROM [database_name].
If you need to remove 10 million rows and have 1 GB of log space available use Delete TOP(10000) From dbo. myTable (with your select clause) and keep running it till there are no more rows to delete.
If you want to delete the records of a table with a large number of records but keep some of the records, You can save the required records in a similar table and truncate the main table and then return the saved records to the main table.
The code you tried is in fact two statements. A DELETE
followed by a SELECT
.
You don't define TOP
as ordered by what.
For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.
;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE
May be better for sql2005+ to use:
DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions
For Sql2000:
DELETE FROM [MyTab]
WHERE YourIdField IN
(
SELECT TOP 1000
YourIdField
FROM [MyTab]
WHERE YourConditions
)
BUT
If you want to delete specific subset of rows instead of arbitrary subset, you should explicitly specify order to subquery:
DELETE FROM [MyTab]
WHERE YourIdField IN
(
SELECT TOP 1000
YourIdField
FROM [MyTab]
WHERE YourConditions
ORDER BY ExplicitSortOrder
)
Thanks tp @gbn for mentioning and demanding the more clear and exact answer.
As defined in the link below, you can delete in a straight forward manner
USE AdventureWorks2008R2;
GO
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO
http://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx
delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)
SET ROWCOUNT 1000;
DELETE FROM [MyTable] WHERE .....
It is fast. Try it:
DELETE FROM YourTABLE
FROM (SELECT TOP XX PK FROM YourTABLE) tbl
WHERE YourTABLE.PK = tbl.PK
Replace YourTABLE
by table name,
XX
by a number, for example 1000,
pk
is the name of the primary key field of your table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With