Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

delete top 100 Expand / Collapse
Author
Message
Posted Thursday, July 18, 2013 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
GilaMonster (7/17/2013)
Sean Lange (7/17/2013)
The next issue is top(1000). You have no order by so there is no way to know which 1000 it will delete.


You can't put an Order By on a delete.

DELETE TOP (100) FROM SomeTable
ORDER BY ID

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ORDER'.


You can put an order by into a CTE (the select) and delete from the CTE, you can put a select with order by and use IN, but you can't put an order by onto a delete directly


I know you can't do it directly but the point is valid. Issuing a delete top(x) with no way to order them makes no sense, unless of course the delete is intended to be inside a batching delete process where the order doesn't matter as long as they all get deleted.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1475027
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse