Getting server error on Delete

  • Hi. Ran into an error message trying a delete: Server: Msg 8624, Level 16, State 3, Line 10 - Internal SQL Server error.

    When attempting the Delete below.. with a NOT IN on the WHERE clause which included a GROUP BY statement. I trimmed out the code as below (which probably makes the intent of the original query rather meaningless)and can work around it easy enough but could not find a limitation on using a GROUP BY in this manner. There was a MS Bug noted and I upgraded to SQL 2000 SP4 which is supposed to have fixed it.

    If I comment out the GROUP BY it works without error. I am interested in whether you know if there is a restriction on usage of GROUP BY and where that is stated and/or your opinions/experiences.

    Thank you

    Toni

    Declare @tempt table (tblid int identity, tblchar varchar(5))

    insert into @tempt (tblchar) -- load some data

    select 'abcd' union all

    select 'bcd' union all

    select 'cd'

    delete from @tempt -- try the delete

    where tblid not in

    (

    select t2.tblid

    from @tempt t1

    join @tempt t2

    on t1.tblid = t2.tblid and t1.tblid in

    (

    select min(tblid)

    from @tempt

    )

    group by t2.tblid -- If the GROUP BY is commented out all is fine

    )

  • Look in the BOL, use the Index tab, enter DELETE, then choose the option for DELETE (Described). That will show the acceptable syntax for the DELETE command. The only place you will see GROUP BY is if you use OPTION for optimizer hints.

    -SQLBill

  • Thanks Bill. /* added a little more */

    I did see a post out on microsoft support about using MAXDOP 5 as an OPTION hint and tried that with no effect. I would think if you could specify how you want SQL to do the GROUP on a DELETE (hash or merge), then it should mean that it's ok to use group by on the delete - no? So I am still wondering why the server error?

    Had also looked through BOL to the best of my ability.

    Have now tried with both types of GROUP hints and still get the server error.

    Toni

  • This works:

    [Code]

    Declare @tempt table (tblid int identity, tblchar varchar(5))

    insert into @tempt (tblchar) -- load some data

    select 'abcd' union all

    select 'bcd' union all

    select 'cd'

    delete T

    from @tempt T -- try the delete

    WHERE NOT EXISTS (

    select t2.tblid

    from @tempt t1

    join @tempt t2

    on t1.tblid = t2.tblid and t1.tblid in

    (

    select min(tblid)

    from @tempt

    )

    WHERE T.tblid = t2.tblid

    group by t2.tblid -- If the GROUP BY is commented out all is fine

    )

    [/Code]

    But, honestly, can you explain what that "GROUP BY" is for?

    It does not change anything really, just adds unnecessary overhead.

    _____________
    Code for TallyGenerator

  • Thanks Sergiy. The GROUP BY was left over from having an aggregate function in the SELECT statement that I was using to test to make sure I got the right records.

    That was basically what I did to get around it... figured out that the GROUP BY no longer added anything to the query and removed it. Maybe MS figured why would anyone do this so the code ends up in a Server Error.

    Thanks for the other alternative in case I ever need it - but looks like I never will! I just get curious when something seems out of sorts.

    Thanks again.

Viewing 5 posts - 1 through 4 (of 4 total)

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