Solved

DELETE FROM FROM

Posted on 2014-09-28
8
327 Views
Last Modified: 2014-09-29
( 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
0
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 175 total points
ID: 40349235
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 40349264
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
 
LVL 25

Assisted Solution

by:chaau
chaau earned 175 total points
ID: 40349275
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40349363
Can't see any difference between that and a simple DELETE  FROM table_a
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 75 total points
ID: 40349372
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 40350449
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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40350862
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
 
LVL 65

Author Comment

by:Jim Horn
ID: 40350965
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question