Delete with inner join on another table with where clauses

Question

Delete with inner join on another table with where clauses

luissantos

SSCertifiable

Points: 7391
More actions
February 25, 2010 at 4:50 pm

#145522

Hello comunity
I have the following Script with an inner join :
delete
FROM ml INNER JOIN
pc ON ml.conta = pc.conta
where pc.integracao =1 and pc.ano=2010 and year(ml.data)=2010
This script is correct??
if not, what the better way to do this
Many thanks
Luis Santos

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

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

FarNorthDBA SSCrazy Points: 2075 More actions · Answer 1

Your script is correct and that is a good way to do deletes when you need to reference values in another table to find your records.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

Actually, you've used a form of DELETE that will usually work but can have some serious problems sometime in the future. My recommendation is that you must use both FROM clauses that are available in the DELETE command whenever there is a JOIN or you take a chance on locking up several CPU's for 20 minutes while the server recompiles the command on every row to do a 2 second delete. It won't always happen but it will happen someday. If you look at all the examples in Books Online, anytime there is any type of JOIN, the table being deleted from is always in both FROM clauses and that's something you should definitely follow. Like this...

DELETE FROM ml

FROM ml

INNER JOIN pc

ON ml.Contra = pc.contra

WHERE pc.integracao = 1

AND pc.ano = 2010

AND YEAR(ml.data) = 2010

Most people don't actually type the first FROM in their code because it seems confusing. You can imply the first FROM clause if you prefer but it still must be used... thusly...

DELETE ml --<----<<<< "FROM" removed but still implied

FROM ml

INNER JOIN pc

ON ml.Contra = pc.contra

WHERE pc.integracao = 1

AND pc.ano = 2010

AND YEAR(ml.data) = 2010

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Thanks for your replies, i use the Script send by jeff and they work very fine.

Also, i understand now better the concept about using DELETE with INNER JOIN

Luis Santos