Home Forums SQL Server 2012 SQL 2012 - General DELETE FIRST 1000 ROWS RE: DELETE FIRST 1000 ROWS

  • March 31, 2014 at 5:05 am

    #1701533

    GilaMonster (3/31/2014)

    Lynn Pettis (3/29/2014)

    DELETE TOP (1000) FROM MyTable ORDER BY yourOrderingColumn

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'ORDER'.

    Amusingly enough, while updates and deletes can take a TOP, they can't have an ORDER BY clause.

    To do a delete of the first x rows, ordered by something, this is what's needed:

    DELETE FROM MyTable WHERE UniqueColumn IN (SELECT TOP(n) UniqueColumn FROM MyTable ORDER BY ColumnWhichDeterminesOrder)

    Thanks, don't know what I was thinking. Must be these 7 x 12+ hour days, not always thinking straight toward the end of the day.