SQL POP Quiz
-
Here is a great SQL Problem. I actually found this in some application code.
Given the following tables:
create table tblHeader (ID int)
create table tblDetail (ID int, MID int)
create table tblItem (ID int, DID int)
With the following data:
insert into tblHeader values(1)
insert into tblHeader values(2)
insert into tblHeader values(3)
insert into tblHeader values(4)
insert into tblDetail values(1,1)
insert into tblDetail values(2,1)
insert into tblDetail values(3,1)
insert into tblDetail values(4,1)
insert into tblDetail values(1,2)
insert into tblDetail values(2,2)
insert into tblDetail values(3,2)
insert into tblDetail values(4,2)
insert into tblItem values(1,1)
insert into tblItem values(2,1)
insert into tblItem values(3,1)
insert into tblItem values(4,1)
insert into tblItem values(1,2)
insert into tblItem values(2,2)
insert into tblItem values(3,2)
insert into tblItem values(4,2)
How many rows will be deleted by the following statement (take half credit if you have to create the tables to get the answer):
Delete from tblItem where DID in (Select DID from tblHeader A inner join tblDetail B on A.ID=B.MID Where A.ID = 1)
-
I'll go first..no laughing if Im wrong. Now maybe I should create the tables and see!
4!
Andy
-
Wait. Here is the subquery:
Select
DID
from tblHeader A
inner join tblDetail B
on A.ID=B.MID
Where A.ID = 1There is no DID column in tblHeader or tblDetail, which raises a flag. Is the puzzle correct as written?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Actually from what you typed you will get an error as no DID exists in rblHeader or tblDetail.
See Here
create table tblHeader (ID int)
create table tblDetail (ID int, MID int)
your subquery
Select DID from tblHeader A inner join tblDetail B on A.ID=B.MID Where A.ID = 1
Now if you meant SELECT B.ID then you could have saved logging by doing TRUNCATE TABLE instaed. All 8 will be deleted.
A = 1
Then B returns IDs 1,2,3,4 because of MID = 1
And so since there are only 2 different DIDs 1,2 and 1,2 are returned by B.ID from the suquery you delete all.
Now if you actually meant B.MID instead of DID from the subquery then it would be 4 as only 1 would return anyway.
And no tables needed to be built.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Why??
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
The DELETE works (build the tables). I'm trying to confirm the puzzle is correctly stated in its current form. If it is, he is an evil, evil man (or rather, the developer who actually coded it).
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
D@&^&IT
That is one of those stupid things you know and forget. A suqeury can use as a reference any column from the outer query. That is why you can this. Then of course if you just read it your forget this because you view the subquery entity as a query unto itself not reflecting the outer queres data. Saw it as soon as you stated the delete works. That is how we can do exists crap or in crap like this.
SELECT * FROM tblX WHERE EXISTS (SELECT * FROM tblY WHERE tblY.XID = tblX.ID)
Duhh....That was a good one.
Devil Be Gone!!!!
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 07/12/2002 08:39:45 AM
-
The real question is, though, if the puzzle is correct as written, why obfuscate the code in such a way?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
hi,
I have worked on the statement and i have discoverd this:
Delete from tblItem where field1 in (Select did )
where field1 is a field in the table
this statement is the same:
delete from tblItem where field1 = did
bye
Antonio
-
I am betting it is as he wrote it.
quote:
Here is a great SQL Problem. I actually found this in some application code.What most likely happened is someone found too many records were being deleted and this is what he found. Bad programmers have been known to do worse. I too would like to know if that is what happened.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
My vote is for crappy programming!
Must have been a developer, not a DBA
Steve Jones
-
I really did find this code in an application. You are all right in that it was not coded to produce the desired results (i.e. delete the records from the table that were attached to the header record). The developer was so frustrated that the table go cleared without reason that he actually added a delete trigger to the table that emailed him whenever the table got empty. Too bad he did not realize that it happened EVERY time someone deleted a header record.
It becomes obvious that something is wrong when you replace 'delete' with 'select *', something I strongly recomment to all of my developers.
-
I agree on that one - doing a select first is a very sound principal!
Andy
-
Didn't we cover this in a thread a few months back... running a select before the delete to be sure only the desired set was affected?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply