Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

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



Group: General Forum Members
Last Login: Tuesday, December 6, 2016 8:08 PM
Points: 16,145, Visits: 16,850
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

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 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