Tag: DELETE

I’m not sure if it’s a good sign or a bad sign if that is the message that greets you when you sign into a chat room. It conjures up a response somewhere along the lines of “…oh no” but I like helping out and the person who asked this is bright and passionate about SQL Server; just not fully experienced with it yet.

The Code:

So, drinking my first (of many) coffee of the day, I asked him what was wrong with it.

I have two tables. 1 with values 1,2,3 & the other with values 1,2,3,4,5. When I use delete exists, it should just delete 1,2,3 but table1 is always empty.

Hmmm, not an unreasonable assumption I suppose so I asked him for his code.

DECLARE @t1 table (id1 int);
DECLARE @t2 table (id2 int);

INSERT INTO @t1
VALUES (1),
 (2),
 (3),
 (4),
 (5);
INSERT INTO @t2
VALUES (1),
 (2),
 (3);

DELETE FROM @t1
WHERE EXISTS
 ( SELECT *
 FROM @t1 AS d1
 JOIN @t2 AS d2
 ON d1.id1 = d2.id2
 );

SELECT *
FROM @t1;

That should return 4 and 5 but @t1 is empty! What’s wrong with it?

You may know…

…what the problem is here, I knew what the problem was here. My question for you though is how would you explain it?

I’ll give you my go but you make your own. Here’s the basic of that conversation.

You’re deleting everything from @t1 if your exists returns any rows.
You’re not limiting it at all. You need to remove the second call to the table, the one in your EXISTS, and link it back.
DELETE FROM @t1 AS t1 WHERE EXISTS( SELECT * FROM @t2 AS t2 WHERE t1.id1 = t2.id2)

> Ok, but when it like DELETE FROM @t1 WHERE EXISTS(SELECT * FROM @t2) it should return 4 and 5 too because @t2 just has 1,2,3.

Nope, you’re saying delete from table1 if your exists (RETURNS ANYTHING AT ALL) because you’re not specifying a link back to the first table

> but SELECT * FROM @t2 returns 1,2,3 and @t1 has 1,2,3,4,5?

Yeah but EXISTS technically returns a TRUE or a FALSE. So you’re saying DELETE if TRUE, not DELETE if table1 = table2.

> ahhhhh! Ok I got’cha now

I do not like that explanation though…

It seemed to work, for him at least but I don’t really think that is the best way to explain it.

I had to specify two things

1. EXISTS is about TRUE or FALSE
2. If you want to be selective, you need to link back.

My problem is the documentation on EXISTS says (abbreviated)…

Specifies a subquery to test for the existence of rows.

[…]

Result Types

Boolean

Result Values

Returns TRUE if a subquery contains any rows.

…and I’m not sure if that is any better of an explanation.

What I am sure of though is, if I want to continue to help out, I’ll need to know these topics implicitly and be able to explain them properly.

How would you explain EXISTS?

Let me know, and remember that your explanation should be able to explain this code by Adam Machanic ( twitter ) and Steve Jones ( twitter | blog ).

Be careful! Run this piece of code, the results may not be what you think

SELECT
*
FROM ( VALUES ( 1), ( 2) ) AS x ( i )
WHERE EXISTS ( SELECT MAX(y.i)
FROM
( VALUES ( 1) ) AS y ( i )
WHERE
y.i = x.i );

 

Best o’luck!