Now please sit back and relax, get some tissue, and read on…
Dear FROM Clause,I know that Valentine’s Day is less than a week away, but I felt it was important to write you this letter… I had to put my feelings down on paper. I don’t know quite how to tell you this, but… well… our relationship cannot continue.
It’s just too confusing and complex, and there have been times when it has become… dare I say it?… dangerous.
I still think back to when we were first introduced and we went to NorthWind and AdventureWorks together and started to do SELECTs.
use AdventureWorksThose were fun times… we’d just hang out with your pals WHERE and ORDER BY and sometimes GROUP BY would tag along... there were always a lot of laughs when HAVING showed up… he’s a riot! Occasionally CROSS APPLY would come too… I had a little trouble understanding him at first, but now I think he’s the coolest guy around! I’ve written about him in my online diary several times.
select FirstName,LastName
from Person.Contact
And of course, your table friends would always JOIN the party… and sometimes it would be quite a wild party:
/*We were just friends during those times… it was relaxing and fun and there was no pressure. And then…
Suggest 3 alternate Clothing/Accessory Products
to the @CustomerID that (s)he has never bought before
when (s)he is looking at a particular @ProductID.
(Customers who bought this item also bought...)
See bradsruminations.blogspot.com/2009/07/customers-who-bought-this-item-also.html
*/
use AdventureWorks
select top (3) SuggProdID=d2.ProductID
from Sales.SalesOrderDetail d1
join Sales.SalesOrderHeader h1 on d1.SalesOrderID=h1.SalesOrderID
join Sales.Customer c on h1.CustomerID=c.CustomerID
join Sales.SalesOrderHeader h2 on h1.CustomerID=h2.CustomerID
join Sales.SalesOrderDetail d2 on h2.SalesOrderID=d2.SalesOrderID
join Production.Product p on d2.ProductID=p.ProductID
join Production.ProductSubCategory sc on p.ProductSubCategoryID=sc.ProductSubCategoryID
where d1.ProductID=@ProductID
and h1.CustomerID<>@CustomerID
and c.CustomerType='I'
and p.ProductSubCategoryID<>(select ProductSubCategoryID
from Production.Product
where ProductID=@ProductID)
and sc.ProductCategoryID in (3,4)
and d2.ProductID not in (select ProductID
from Sales.SalesOrderDetail sod
join Sales.SalesOrderHeader soh
on sod.SalesOrderID=soh.SalesOrderID
where CustomerID=@CustomerID)
group by d2.ProductID
having count(distinct h2.SalesOrderID)>5
order by count(distinct h2.SalesOrderID) desc
You asked me out on an UPDATE.
It was a little awkward at first, but after our second and third UPDATEs (and beyond), I started to feel a little more comfortable as we got to know each other better.
/*Then you wanted to have more variety in our UPDATEs… it started to become a little overwhelming and I had to admit, I was starting to get a little confused with our relationship.
Howard Snyder's company changed its name.
We updated it in the Customers table, so now we need to
update the ShipName in all the company's unshipped orders.
*/
use NorthWind
update Orders
set ShipName=Customers.CompanyName
from Orders
join Customers on Orders.CustomerID=Customers.CustomerID
where Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null
/*I started to get worried, because sometimes it seemed as if our relationship wasn’t going anywhere specific… there was no direction… no specific goals… it was kind of non-deterministic.
The query plans for all following updates
are equivalent in every way
*/
update Orders
set ShipName=Customers.CompanyName
from Orders
join Customers on Orders.CustomerID=Customers.CustomerID
where Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null
update Orders
set ShipName=Customers.CompanyName
from Customers /* No Orders table in FROM clause */
where Customers.CustomerID=Orders.CustomerID /* But connected via WHERE */
and Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null
update o /* Update alias of the FROM clause */
set ShipName=c.CompanyName
from Orders o
join Customers c on o.CustomerID=c.CustomerID
where c.ContactName='Howard Snyder'
and o.ShippedDate is null
update Orders /* Note the alias in the FROM clause is ignored here */
set ShipName=c.CompanyName
from Orders as SomeObnoxiousAlias
join Customers c on SomeObnoxiousAlias.CustomerID=c.CustomerID
where c.ContactName='Howard Snyder'
and SomeObnoxiousAlias.ShippedDate is null
/*And then eventually our relationship moved to the next level.
Which of ERNSH's 30 orders will end up
updating his Country column?
We really have no idea.
*/
update Customers
set Country=Orders.ShipCountry
from Customers
join Orders on Customers.CustomerID=Orders.CustomerID
where Customers.CustomerID='ERNSH'
We started… well, y’know (blush)… doing it… the D-Word… DELETEing.
At first it was kind of exciting but really scary at the same time. And I have to admit that I felt a little guilty about all these new-found feelings of power awakening inside me.
But then you started to come on too strong… you were smothering me… it was like you were there all the time…
/* Two FROMs in a DELETE is kind of confusing */I needed some space… And I asked you to pull back and not let things go so fast…
delete from Customers
from Customers
where CustomerID='FISSA'
/* But the first FROM is optional */Then things got weird. You wanted to get into all this… experimentation. We starting DELETEing in ways that were really unsettling and… unnatural. My SQL Server MVP friends Steve Kass and Aaron Alton warned me about you, but I didn’t listen. It was becoming dangerous, and we started doing things we really shouldn’t be doing… and then one thing would lead to another, and… well I still feel great pangs of guilt and sorrow when I think back on how I had to sometimes abort the query to avoid undesirable results.
delete Customers
from Customers
where CustomerID='FISSA'
/* And FROM, of course, is not needed if there's only one table */
delete Customers
where CustomerID='FISSA'
/*And so I’ve come to the decision that we can no longer have a DELETE relationship… ever again. I’m sorry it has to be this way, but I need to have more control of my life. If I have the urge to DELETE, I will just have to be more careful and use protection… I’ll always use EXISTS or IN… our friend WHERE can get them for me.
The following will delete all of FISSA's orders.
Potentially confusing.
*/
delete Orders
from Orders Customers
where CustomerID='FISSA'
/*
The following will delete the Customer named FISSA.
Really confusing? Yep.
*/
delete Orders
from Customers Orders
where CustomerID='FISSA'
/*
OOPS! The following will end up deleting ALL Order Detail rows.
Notice there's no relationship between the table whose rows we're
deleting and the table referenced in the FROM clause.
As long as there is one single order in the Orders table
with a CustomerID of ERNSH, the Order Details table will be
completely cleared out... *Poof*... Nothing... Nada... Zip.
*/
delete [Order Details]
from Orders
where CustomerID='ERNSH'
/*Things won’t be so confusing anymore… my DELETE life will be more straightforward and easier to understand and… well… normal.
Delete all Order Details rows that belong
to the Customer ERNSH.
The two queries below are equivalent in every way.
*/
delete [Order Details]
where exists (select *
from Orders
where OrderID=[Order Details].OrderID
and CustomerID='ERNSH')
delete [Order Details]
where OrderID in (select OrderID
from Orders
where CustomerID='ERNSH')
/*I’m also considering ending our UPDATEing relationship. I’m becoming more involved in a query organization called CTE and that’s helping me move into another direction.
Delete all SalesOrderDetail rows that satisfy all of the following:
1) The order was placed by a guy named Richard with a SuperiorCard Credit Card
2) The line item involved a Special Offer of the Reseller Category
3) The line item involved a Product of the Accessories Category
*/
use AdventureWorks
delete Sales.SalesOrderDetail
where SalesOrderID in (select SalesOrderID
from Sales.SalesOrderHeader soh
join Person.Contact c on soh.ContactID=c.ContactID
join Sales.CreditCard cc on soh.CreditCardID=cc.CreditCardID
where c.FirstName='Richard'
and cc.CardType='SuperiorCard')
and SpecialOfferID in (select SpecialOfferID
from Sales.SpecialOffer
where Category='Reseller')
and ProductID in (select ProductID
from Production.Product p
join Production.ProductSubCategory ps
on p.ProductSubCategoryID=ps.ProductSubCategoryID
join Production.ProductCategory pc
on ps.ProductCategoryID=pc.ProductCategoryID
where pc.Name='Accessories')
use NorthWind;I realize that you will still be peripherally involved in my UPDATEing and DELETEing, watching from the sidelines, but I’ll just have to accept that.
with TargetRows as
(
select ShipName
,NewShipName=Customers.CompanyName
from Orders
join Customers on Orders.CustomerID=Customers.CustomerID
where Customers.ContactName='Howard Snyder'
and Orders.ShippedDate is null
)
update TargetRows
set ShipName=NewShipName
To be brutally honest, I’ve considered breaking off from you completely, but that’s just not possible… We still have to work together, and I’m not about to go out and get another job just to avoid you.
I hope you understand all the things I’ve written in this letter. I’m sure it’s hard reading something like this, but I think over time you will understand and accept my decision.
I’m sure we can still be friends.
Sincerely,
Every SQL Developer in the Universe (I hope)
Posts
