Jak usunąć 1000 pierwszych wierszy z tabeli za pomocą Sql Server 2008?


108

Mam tabelę w SQL Server. Chciałbym usunąć z niego 1000 pierwszych wierszy. Jednak próbowałem tego, ale zamiast po prostu usunąć pierwsze 1000 wierszy, usunąłem wszystkie wiersze w tabeli.

Oto kod:

delete from [mytab] 
select top 1000 
a1,a2,a3
from [mytab]

8
Potrzebujesz ORDER BY, aby TOP miał znaczenie: zobacz odpowiedź @Martin Smith, która jest jedyną z pięciu, która to ma. Czasami rozpaczam
gbn

2
Czy chcesz usunąć jakieś 1000 wierszy? Po prostu wybrana losowo? Czy na przykład 1000 pierwszych najstarszych wierszy?
Nick Chammas,

13
Usunąłeś całą tabelę, ponieważ delete from [mytab]jest to jedna instrukcja, a select top ...druga.
Nick Chammas,

2
Nie musisz zamawiać na top, zależy, dlaczego robisz TOP. Jeśli chcesz usunąć 10 milionów wierszy i mieć 1 GB wolnego miejsca na dzienniki, użyj polecenia Usuń TOP (10000) z dbo.myTable (z klauzulą ​​select) i kontynuuj, aż nie będzie już żadnych wierszy do usunięcia. Kogo obchodzi, czy to arbitralne. Sortowanie tylko spowalnia zapytanie.
tvanharp

1
Zdaję sobie sprawę, że jest to odwieczne pytanie (od SO lat), ale myślę, że ważne jest, aby ludzie wzięli pod uwagę komentarze @gbn . Chociaż jego komentarze nie odnoszą się do mojej sytuacji (próba usunięcia bloków rekordów bez powodowania problemów z LOCK, ale tak naprawdę nie dbając o kolejność, w jakiej są usuwane), mogą one najprawdopodobniej dotyczyć TWOJEJ sytuacji. Upewnij się, że rozważysz je, zanim ślepo skorzystasz z poniższych odpowiedzi, które nie zawierają klauzuli ORDER BY.
Andrew Steitz

Odpowiedzi:


196

Kod, który wypróbowałeś, to w rzeczywistości dwie instrukcje. A, DELETEpo którym następuje SELECT.

Nie definiujesz TOPjako uporządkowany według czego.

W przypadku określonych kryteriów porządkowania najbardziej efektywnym sposobem jest usunięcie z CTE lub podobnego wyrażenia tabeli.

;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE

13
Dla tych, którzy zastanawiają się, dlaczego nie możesz tego zrobić DELETE TOP (1000) FROM table ORDER BY column, przeczytaj to : „Wiersze, do których odwołuje się wyrażenie TOP używane z poleceniami INSERT, UPDATE, MERGE lub DELETE nie są ułożone w żadnej kolejności”.
Nick Chammas,

3
@Magnus yes. Ale nie 2000. Możliwe jest użycie tabeli pochodnej w 2000 roku. Nie mam w pobliżu instancji, z którą mógłbym przetestować.
Martin Smith


2
Zrobiłem trochę inaczej (chociaż myślę, że CTE może być ładniejsze): DELETE T1 FROM (SELECT TOP 1000 * FROM [MYTAB] ORDER BY A1) T1;
Abacus

4
@Liam - tylko dlatego, że jeśli przed CTE znajduje się jakiekolwiek poprzednie oświadczenie, należy je zakończyć średnikiem, więc dołączenie go na początku WITHpoprzedza skargi od osób, które tego nie zrobiły.
Martin Smith

86

Może być lepszy w użyciu dla sql2005 +:

DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions

Dla Sql2000:

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
)

ALE

Jeśli chcesz usunąć określony podzbiór wierszy zamiast dowolnego podzbioru, powinieneś jawnie określić kolejność podzapytania:

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
  ORDER BY ExplicitSortOrder
)

Dzięki tp @gbn za wspomnienie i zażądanie bardziej jasnej i dokładnej odpowiedzi.


3
@gbn Może dla Ciebie bezużyteczne, ale wciąż o to właśnie chodzi w pytaniu.
Joachim Isaksson

1
@Joachim Isaksson: idź i poczytaj o TOP, a potem wróć. Nie ma czegoś takiego jak TOP bez ORDER BY w zestawach. Ewentualnie przejdź i znajdź kanoniczne odniesienie, które dowodzi, że się mylę ... Aby zaoszczędzić na wyszukiwaniu, sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/ ... i blogs.technet.com/b/wardpond/archive /
2007/07/19

1
@gbn Brak warunków dotyczących KTÓRYCH wierszy do usunięcia, więc ORDER BY w podzapytaniu jest bezużyteczne
Oleg Dok

1
@gbn Czy wspominałeś GDZIE w podzapytaniu - filtruję 1000 dowolnych wierszy w ramach wybranych kryteriów, a następnie usuwam. Prawidłowy scenariusz? Tak. Jeśli dodam ORDER BY NEWID () lub cokolwiek to nic nie zmieni - nadal usuwam 1000 wierszy przefiltrowanych według wybranych kryteriów
Oleg Dok

8
@gbn W przypadku, gdy szukasz prawidłowego użycia TOP bez ORDER BY: co mnie tutaj przyniosło, to muszę usunąć wszystkie wiersze pasujące do niektórych kryteriów, ale ze względu na wydajność nie chcę, aby usunęło więcej niż 10000 wierszy na czas. Nie obchodzi mnie, które wiersze usuwa, ponieważ uruchomię polecenie ponownie w pewnych odstępach czasu, aż wszystkie takie wiersze znikną.
Richiban,


6
delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)

4
Bezużyteczne: TOP bez ORDER BY daje dowolne wiersze
gbn

4
@gbn Może dla Ciebie bezużyteczne, ale wciąż o to właśnie chodzi w pytaniu.
Joachim Isaksson

3
@gbn Nie twierdziłem, że istnieje domyślna kolejność sortowania lub że zapytanie jest w ogóle przydatne, przypomniałem tylko, że pytanie o to nie pytało, więc co sugerowałbyś zamawiać?
Joachim Isaksson

2
@gbn Nie wiem, dlaczego jesteś tak wrogo nastawiony do wszystkich z powodu czegoś, co jest punktem wyjścia. Nie twierdzę, że moja odpowiedź to koniec wszystkiego, to tylko sugestia, aby komuś pomóc. Myślę, że znaczenie mają klucze, które wracają z zapytania podrzędnego.
Jason Dam

2
To może być wszystko, czego szuka pytający. Chciałbym tylko dodać uwagę dla innych czytających, aby podkreślić, że wiersze usunięte przez takie stwierdzenie nie mają gwarancji, że będą w jakiejkolwiek kolejności.
Nick Chammas,

3
SET ROWCOUNT 1000;

DELETE FROM [MyTable] WHERE .....

2
Czy to naprawdę ma znaczenie przy zaledwie 1000 rzędach? Gdyby było 100 000 000 wierszy, to punkty mogą być ważne, ale dla zaledwie 1000 wierszy jest to zdecydowanie najprostsze rozwiązanie zaproponowane do tej pory dla SQL 2008.
Joe Bourne

1

To jest szybkie. Spróbuj:

DELETE FROM YourTABLE
FROM (SELECT TOP XX PK FROM YourTABLE) tbl
WHERE YourTABLE.PK = tbl.PK

Zastąp YourTABLEnazwą tabeli, XXliczbą, na przykład 1000, pkjest nazwą pola klucza podstawowego tabeli.


Skutecznie tworzysz dwie tabele z jednej, a następnie usuwasz w miejscu połączenia. Działa dobrze, gdy chcesz usunąć najstarsze (lub najnowsze) rekordy z tabeli, ponieważ możesz najpierw posortować je rosnąco. Ten t-sql jest akceptowany przez firmę Microsoft (i jest szybki).
Tequila
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.