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

  • March 31, 2014 at 2:15 am

    #1701500

    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)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass