Error in Simple Delete...Please Help

  • Hello experts,

    I’m having a challenge in writing a simple delete statement. I’ve a table (FirstLevelChangeDetail) which has same number to 2 fields (CapturedDate & DbTotal) however there is the 3rd entity which is RecordNumber and it’s different. I’ve 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 what’s wrong and how I can fix it?

    Thanks a lot in advance.

  • You cannot use an ORDER BY clause in a DELETE statement. Wouldn't make any sense anyway.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • mr_adeelalisyed

    You may want to use code like this:

    CREATE TABLE #FirstLevelChangeDetail(CapturedDate DATETIME,DbTotal INT,RecordNumber INT)

    INSERT INTO #FirstLevelChangeDetail

    SELECT '1/1/09',20,1 UNION ALL

    SELECT '1/1/09',50,3 UNION ALL

    SELECT '1/1/09',21,5 UNION ALL

    SELECT '1/2/09',50,1 UNION ALL

    SELECT '1/3/09',50,1

    ;with numbered as(SELECT rowno=row_number() over

    (partition by CapturedDate order by RecordNumber),CapturedDate, DbTotal,RecordNumber

    from #FirstLevelChangeDetail)

    --For testing use:

    SELECT * FROM numbered --To check if output is correct

    --Once tested and output is correct replace above statement with

    --DELETE from numbered WHERE rowno > 1

    SELECT results:

    rownoCaptured Date DbTotal RecordNumber

    12009-01-01 00:00:00.000 201

    22009-01-01 00:00:00.000 503

    32009-01-01 00:00:00.000 215

    12009-01-02 00:00:00.000 501

    12009-01-03 00:00:00.000 501

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply