Help me with the SYNTAX for the DELETE statement

Question

Post reply

Help me with the SYNTAX for the DELETE statement

mw_sql_developer

SSCoach

Points: 19445
More actions
September 24, 2015 at 10:02 am

#320861

I have a table where I need to delete rows if the member does not exists in the member table.
Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL

Viewing 15 posts - 1 through 15 (of 19 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If Gender is a non nullable column, it will work.

I'd usually go for something like this:

Delete T

FROM Event_Temp_Lead_Screen T

WHERE NOT EXISTS( SELECT 1 FROM member M WHERE M.MemberID = T.MemberID )

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Luis Cazares (9/24/2015)
If Gender is a non nullable column, it will work.
I'd usually go for something like this:
Delete T
FROM Event_Temp_Lead_Screen T
WHERE NOT EXISTS( SELECT 1 FROM member M WHERE M.MemberID = T.MemberID )

This. It's clearer than the left join as to what's been done.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

This is just fine

DECLARE @t TABLE (memberID int);

DECLARE @m TABLE (memberID int);

INSERT @m VALUES (1),(2),(4);

INSERT @t VALUES (1),(2),(3),(4);

SELECT * FROM @t;

DELETE T

FROM @t T

LEFT JOIN @m M on T.memberID = M.memberID

WHERE M.memberID IS NULL

SELECT * FROM @t;

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

using the same sample data from above and taking Luis' solution you can also do this:

WITH T AS

(

SELECT T.*

FROM @t T

WHERE NOT EXISTS( SELECT 1 FROM @m M WHERE M.MemberID = T.MemberID )

)

DELETE FROM T;

What's good about this solution is (when you have perm tables) you can highlight the query inside the CTE to see what you are going to delete. This makes testing/troubleshooting a little easier than doing it based on a join.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

mw_sql_developer

SSCoach

Points: 19445

September 24, 2015 at 10:35 am

#1829222

Great stuff

Thanks

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 6

Alan.B (9/24/2015)
What's good about this solution is (when you have perm tables) you can highlight the query inside the CTE to see what you are going to delete. This makes testing/troubleshooting a little easier than doing it based on a join.

Just in case of interest, we do that with the syntax mentioned earlier

DELETE T

-- SELECT COUNT(*)

-- SELECT TOP 100 T.Col1, T.Col2, ...

FROM @SomeTable AS T

LEFT JOIN SomeOtherTable AS M

on T.memberID = M.memberID

WHERE M.memberID IS NULL -- or some other criteria

I highlight from one of the commented out SELECT lines (but excluding the "--" natch!) to the bottom to see either the Count or some Columns.

I highlight from the bottom-up, execute the appropriate SELECT to check that all is as expected and then we use Shift-ArrowKeys to extend the already-highlighted selection to include either the other SELECT or, moving the cursor further, the DELETE

Apologies if I am stating the blinking obvious 😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

I prefer the EXISTS approach instead of the OUTER JOIN/IS NULL approach. I think it's much clearer.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 8

Ed Wagner (9/24/2015)
I prefer the EXISTS approach instead of the OUTER JOIN/IS NULL approach. I think it's much clearer.

I agree, although I've got used to the LEFT OUTER JOIN and WHERE PKeyColumn IS NULL as a detection for Not Exists.

There are times were I am messing around with INSERT (if not exists) UPDATE (if exists) and DELETE (where exists), and in that situation I find it easier, and less risk of a coding mistake, to keep the exact same code and just minimally twiddle the OUTER JOIN (for NOT EXISTS on INSERT) to an INNER JOIN (for EXISTS on UPDATE)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

Beleive me, the LEFT OUTER JOIN is faster.

I deal with large tables and the "EXITS" make the query run for hours..

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 10

mw112009 (9/24/2015)
Beleive me, the LEFT OUTER JOIN is faster.
I deal with large tables and the "EXITS" make the query run for hours..

You likely have something else at play than just the difference between LEFT JOIN/IS NULL and NOT EXISTS. At any rate, the blanket statement that LEFT JOIN/IS NULL is faster than NOT EXISTS doesn't hold up.

The comparison between the two methods has been discussed at length elsewhere, so I won't pretend to invent the wheel 🙂

http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Cheers!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Jacob Wilkins (9/24/2015)
mw112009 (9/24/2015)
Beleive me, the LEFT OUTER JOIN is faster.
I deal with large tables and the "EXITS" make the query run for hours..
You likely have something else at play than just the difference between LEFT JOIN/IS NULL and NOT EXISTS. At any rate, the blanket statement that LEFT JOIN/IS NULL is faster than NOT EXISTS doesn't hold up.
The comparison between the two methods has been discussed at length elsewhere, so I won't pretend to invent the wheel 🙂
http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Cheers!

You're correct that the LEFT OUTER JOIN/IS NULL is faster. An even faster way is using EXCEPT. I'll see if I can find the thread where several different approaches where compared.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

mw112009 (9/24/2015)
Beleive me, the LEFT OUTER JOIN is faster.
I deal with large tables and the "EXITS" make the query run for hours..

Are we supposed to take your word for granted disregarding what Aaron Bertrand or Gail Shaw have posted with actual performance testing?

You realize that you just posted on a forum if the syntax is correct which takes away some credibility from your words.

However, you might not be wrong. Share with us what you used to test and compare the methods. Evidence through testing is better than any expert's words.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

Luis Cazares (9/25/2015)
mw112009 (9/24/2015)
Beleive me, the LEFT OUTER JOIN is faster.
I deal with large tables and the "EXITS" make the query run for hours..
Are we supposed to take your word for granted disregarding what Aaron Bertrand or Gail Shaw have posted with actual performance testing?
You realize that you just posted on a forum if the syntax is correct which takes away some credibility from your words.
However, you might not be wrong. Share with us what you used to test and compare the methods. Evidence through testing is better than any expert's words.

There was a thread a while back where the performance of just such a delete was debated. Someone (I believe Eirikur) did a performance comparison of the different methods. The approach using EXCEPT won hands down, but I can't find the thread. If anyone else can find it, I'd very much appreciate a link. I just don't have the knack of using the search here on SSC.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Ed Wagner (9/25/2015)
Luis Cazares (9/25/2015)
mw112009 (9/24/2015)
Beleive me, the LEFT OUTER JOIN is faster.
I deal with large tables and the "EXITS" make the query run for hours..
Are we supposed to take your word for granted disregarding what Aaron Bertrand or Gail Shaw have posted with actual performance testing?
You realize that you just posted on a forum if the syntax is correct which takes away some credibility from your words.
However, you might not be wrong. Share with us what you used to test and compare the methods. Evidence through testing is better than any expert's words.
There was a thread a while back where the performance of just such a delete was debated. Someone (I believe Eirikur) did a performance comparison of the different methods. The approach using EXCEPT won hands down, but I can't find the thread. If anyone else can find it, I'd very much appreciate a link. I just don't have the knack of using the search here on SSC.

Doesn't work for me either. I always use Google with the site hint, like this:

site:sqlservercentral.com eirikur except delete

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden