Getting server error on Delete

Question

Getting server error on Delete

toniupstny

Hall of Fame

Points: 3986
More actions
December 20, 2007 at 11:01 am

#177653

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
)

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 2

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

Sergiy SSC Guru Points: 110192 More actions · Answer 3

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 4

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.