Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Error In Simplet Delete...Please help

    Hello experts,

    Im having a challenge in writing a simple delete statement. Ive a table (FirstLevelChangeDetail) which has same number to 2 fields (CapturedDate & DbTotal) however there is the 3rd entity which is RecordNumber and its different. Ive 5 rows which have same CapturedDate and DbTotal but Different RecordNumber. I still need to delete them even though they are different records. When I wrote the following select statement it pulled exactly the same rows which I need to delete.
    -------------------------------------
    Declare @n int
    select @n = count (RecordNumber)
    from FirstLevelChangeDetail
    where CapturedDate = (select distinct top 1 CapturedDate from FirstLevelChangeDetail order by CapturedDate desc)
    print @n
    set @n = @n-1
    print @n
    select top (@n) *
    from FirstLevelChangeDetail
    order by RecordNumber desc
    -------------------------------------
    However when I replace select top (@n) with delete I end up having an error saying

    Msg 156, Level 15, State 1, Line 11
    Incorrect syntax near the keyword 'order'.

    Here is my delete code.
    -------------------------------------
    Use CIAdeel
    Go
    Declare @n int
    select @n = count (RecordNumber)
    from FirstLevelChangeDetail
    where CapturedDate = (select distinct top 1 CapturedDate from FirstLevelChangeDetail order by CapturedDate desc)
    print @n
    set @n = @n-1
    print @n
    delete top (@n)
    from FirstLevelChangeDetail
    order by RecordNumber desc
    -------------------------------------

    Does anybody see whats wrong and how I can fix it?

    Thanks a lot in advance.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Try
    Code:
    delete FirstLevelChangeDetail
    where RecordNumber in (select top (@n) RecordNumber order by RecordNumber desc)
    This may take some fiddling, if you have duplicate RecordNumbers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •