DELETE FROM FROM

( Disclaimer:  I'm studying for the Microsoft 70-433 SQL 2008 exam.)

What's the reason for if you need to do a DELETE and join on other tables, the syntax is

DELETE
FROM table_a
FROM table_b

Instead of DELETE .. FROM table_a .. JOIN table_b?

Thanks in advance.
Jim
LVL 67
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
The first FROM is an optional keyword. Consider this:
DELETE tableA
FROM tableA INNER JOIN tableB 
ON tableA.ID = tableB.ID

Open in new window

With the optional FROM keyword this syntax will look like:
DELETE FROM tableA
FROM tableA INNER JOIN tableB 
ON tableA.ID = tableB.ID

Open in new window

I personally think it is very confusing and do not use in my queries
0
PortletPaulfreelancerCommented:
If you take off your technical hat for a moment...

delete FROM tableA

is a better English description of what you are about to do

("delete tableA" in English is an instruction to wipe out something called tableA)

Then, the second FROM is the usual FROM Clause

So, while the first FROM is optional, it makes sense if you consider each "sentence" individually
0
chaauCommented:
If you are deleting from tableA only then yes
delete FROM tableA WHERE id = 1

Open in new window

sounds better. In fact I use this syntax.

However in the example with the joins
DELETE tableA
FROM tableA INNER JOIN tableB 
ON tableA.ID = tableB.ID

Open in new window

double FROMs look awkward
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can't see any difference between that and a simple DELETE  FROM table_a
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Was doing some tests and the 2nd FROM it's:

-- This don't works
DELETE
FROM Table2, Table1
WHERE Table2.code = Table1.code

-- This also don't works
DELETE 
FROM Table2
INNER JOIN Table1 ON Table2.code = Table1.code

-- This works
DELETE
FROM Table2
FROM Table1
WHERE Table2.code = Table1.code

-- This also works
DELETE Table2
FROM Table2, Table1
WHERE Table2.code = Table1.code

-- And this also works
DELETE Table2
FROM Table2
INNER JOIN Table1 ON Table2.code = Table1.code

Open in new window

So, looks like 2nd FROM clause is optional and allows you to don't repeat the table name in the FROM clause.
0
Scott PletcherSenior DBACommented:
The first FROM is just for the DELETE.  

I believe the second FROM is to insure consistent ANSI join syntax.  When joining, the first table always a "FROM" before it.  Thus, this method is not only syntactically consistent, it's a standard pattern that's already available in the parser.

Allowing this:
DELETE Table2
INNER JOIN Table1 ON Table2.code = Table1.code
would violate the normal/standard way of doing a join.


My preference to avoid confusion is to:
1) always alias the tables involved (which I always do when joining anyway)
2) use the alias as the UPDATE object (which is strongly recommended by MS anyway)

DELETE FROM t1
FROM dbo.table1 t1
INNER JOIN dbo.table2 t2 ON t2.key_col = t1.key_col
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Most of these make sense, especially Scott's comment about ANSI join syntax.

Reading from the Microsoft Press 70-433 exam book, top of p.54, the example given has two FROM's and a JOIN, without extra verbage to fully explain the FROM - FROM:

Like the INSERT and UPDATE commands, the DELETE command can remove rows in one table based on information returned from a joined table.  To accomplish this, you define a second FROM clause.  The following command removes rows from the Order Details table where the order was placed before July 10, 1996, and the order has shipped:
DELETE FROM [Order Details]
FROM ORDERS JOIN [Order Details]
   ON Orders.OrderID = [Order Details].OrderID
WHERE OrderDate < '07-10-1996' and ShippedDate IS NOT NULL

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
To make it more confusing, the simpler version was on the practice test:

Q: How to delete rows in Products where a related row exists in NewProducts?
A:
DELETE
FROM Products as p1
FROM NewProducts as p2
WHERE p1.id = p2.id

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.