Solved

DELETE FROM FROM

Posted on 2014-09-28
8
239 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 24

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 24

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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40349363
Can't see any difference between that and a simple DELETE  FROM table_a
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

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:
ScottPletcher 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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now