Problem with DELETE stmt

Question

Problem with DELETE stmt

cdun2

SSCertifiable

Points: 7893
More actions
November 9, 2004 at 12:59 pm

#165439

Hello,
I am trying to execute a delete statement that should delete 1925 records from a table that contains 425,000 records total. The following is the delete statement I am using;
Delete tblMMStanTestScores
Select TS.Grade, TS.TestShortName, TS.Permnum, TS.SA, TS.InsertDate
From tblMMStanTestScores TS
Inner Join Student_data_main SD On SD.Permnum=TS.Permnum
Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID
Where
TS.TestShortName = 'SFM1'
and
TS.InsertDate >= '11/04/04'
and
TS.InsertDate < '11/05/04'
and
TS.Grade = '3'
When I attempt to run this DELETE, the 300MB transaction log for the database becomes full, and the delete never occurs.
Is there something wrong with the query, or should I expect that this delete operation would fill up the Transaction Log? What other course of action can I take to delete these records?
Thank you for your help!
CSDunn

Viewing 12 posts - 1 through 11 (of 11 total)

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

The DELETE FROM syntax is pretty odd, and I'm not sure of all of the side effects. Can you use the ANSI Standard DELETE syntax instead:

Delete tblMMStanTestScores

Where TS.TestShortName = 'SFM1'

and TS.InsertDate >= '11/04/04'

and TS.InsertDate < '11/05/04'

and TS.Grade = '3'

and exists

(select *

from Student_data_main SD

Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID

where SD.Permnum=tblMMStanTestScores.Permnum)

--
Adam Machanic
whoisactive

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thank you for your help, I will try this against a temp table.

CSDunn

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

The transaction log becomes full ? <- That's your reason!

DELETE works just like every other transactional command (SELECT / UPDATE / INSERT). When you use DELETE, every row that is about to be deleted has to be recorded in the transaction log, so that SQL Server either COMMITs or ROLLBACKs the change.

When your transaction log becomes full, SQL Server cannot COMMIT the query so it has to ROLLBACK: effectively meaning that nothing is deleted.

Try deleting a smaller number of rows at a time, or increase the log file size for the database so SQL Server can complete.

Julian Kuiters
juliankuiters.id.au

David.Poole SSC Guru Points: 76001 More actions · Answer 4

The other thing to watch out for is the way in which SQL stores dates.

For example, the default SQL Server install gives you dates in US format Month/Day/Year.

In Britain we use Day/Month/Year.

For this reason I always pass dates into my stored procedures as character strings and use the CONVERT function to set internally declared variables to the date equivalents. That way there is no ambiguity as to whether 03/05/2004 is 3rd May or 5th March.

LinkedIn Profile

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Perhaps you should thank your lucky star the log got full. The SQL written isn't quite what you wanted to do..

Delete tblMMStanTestScores

Select TS.Grade, TS.TestShortName, TS.Permnum, TS.SA, TS.InsertDate

From tblMMStanTestScores TS

Inner Join Student_data_main SD On SD.Permnum=TS.Permnum.......

What you have here is two statements - a DELETE without a where clause followed by a SELECT.

So... what is happening, and the reason the log filled up, is just that you're bluntly trying to delete the entire table, then you perform a select against it.

Try the syntax Adam suggested, I believe that is what you want to do.

/Kenneth

Adam Machanic SSCoach Points: 15370 More actions · Answer 6

Kennith,

Good catch; I thought that the OP had, perhaps, added the SELECT in later to make sure the JOINs were correct...

--
Adam Machanic
whoisactive

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Maybe that was the intention, but as it was presented as the actual SQL executed, it boils down to a batch of two statements rather than one.

With some commenting the codeblock can work for both cases.

With the 'delete row' commented out it's a plain check

-- Delete tblMMStanTestScores

Select TS.Grade, TS.TestShortName, TS.Permnum, TS.SA, TS.InsertDate

From tblMMStanTestScores TS

Inner Join Student_data_main SD On SD.Permnum=TS.Permnum

Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID

To actually perform the intended delete op, the 'select row' must be taken out

Delete tblMMStanTestScores

-- Select TS.Grade, TS.TestShortName, TS.Permnum, TS.SA, TS.InsertDate

From tblMMStanTestScores TS

Inner Join Student_data_main SD On SD.Permnum=TS.Permnum

Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID

Also, I'd like to advocate for one more thing whenever doing stuff in Transact-SQL that is not read-only, add this statement as the first everytime you want to do a delete / update;

BEGIN TRAN

.. do your stuff here

It's a habit that will save some grey hairs eventually...

..oh, and don't forget to COMMIT if all goes well

/Kenneth

Adam Machanic SSCoach Points: 15370 More actions · Answer 8

Still, though, that DELETE FROM syntax is pretty weird (although not nearly as bad as the UPDATE FROM syntax.. ugh)

--
Adam Machanic
whoisactive

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Agreed - it has the potential to trip some unwary feet. Tonge-in-cheek is required (as well as some 'sanity checks' like plain selects to verify the 'target area')

/Kenneth

cdun2 SSCertifiable Points: 7893 More actions · Answer 10

Thanks for going the extra mile to assist. I really appreciate your help!

CSDunn

Answer 11

Delete tblMMStanTestScores TS

Inner Join Student_data_main SD On SD.Permnum=TS.Permnum

Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID

Where TS.TestShortName = 'SFM1' and TS.InsertDate >= '11/04/04'

and TS.InsertDate < '11/05/04' and TS.Grade = '3'