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]
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
DELETE TOP (1000) FROM table ORDER BY column, read this: "The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE, or DELETE are not arranged in any order." –
Tussle ;? –
Sestet WITH pre-empts complaints from people that haven't done that. –
Predesignate delete from table where id in (select top 1000 id from table order by a1). In my case with 50 million rows difference was 20% to 80%. –
Extenuation 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.
No need to create CTE to fetch and delete as it will load the records in memory first and then perform the delete operation which might impact the performance.
All you need is simple delete top(n) based on your condition to remove the records from your table which is fast and efficient for non-partitioned table.
delete top(1000)
from [mytab]
where
condition[1]
condition[2]
.
.
condition[n]
I agree with the Hamed elahi and Glorfindel.
My suggestion to add is you can delete and update using aliases
/*
given a table bi_customer_actions
with a field bca_delete_flag of tinyint or bit
and a field bca_add_date of datetime
note: the *if 1=1* structure allows me to fold them and turn them on and off
*/
declare
@Nrows int = 1000
if 1=1 /* testing the inner select */
begin
select top (@Nrows) *
from bi_customer_actions
where bca_delete_flag = 1
order by bca_add_date
end
if 1=1 /* delete or update or select */
begin
--select bca.*
--update bca set bca_delete_flag = 0
delete bca
from (
select top (@Nrows) *
from bi_customer_actions
where bca_delete_flag = 1
order by bca_add_date
) as bca
end
To enhance the accepted answer code, check out mine. The code will start by deleting 1000 rows from the table at a time, and then increase the batch size by 10% after each iteration. This way, the code will delete more rows as the table gets smaller and reduce the number of loops needed. The variable @count will still store the number of rows affected by each delete statement and the loop will stop when it becomes zero.
DECLARE @batch_size INT = 10000
DECLARE @count INT = 1
WHILE @count > 0
BEGIN
;WITH CTE AS
(
SELECT TOP (@batch_size) *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE
SET @count = @@ROWCOUNT
-- Increase the batch size by 10% after each iteration
SET @batch_size = @batch_size * 1.1
END
None of these solutions worked for me, instead I had to open MSSQL Management Studio, right-click on the database and select "Tasks > Shrink > Database".
I'm running SQL Server 2022, so maybe things have changed since most of these answers.
© 2022 - 2024 — McMap. All rights reserved.
delete from [mytab]is one statement, andselect top ...is another. – Tussle