Solved

DELETE FROM FROM

Posted on 2014-09-28
8
248 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 46

Expert Comment

by:Vitor Montalvão
ID: 40349363
Can't see any difference between that and a simple DELETE  FROM table_a
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 46

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

13 Experts available now in Live!

Get 1:1 Help Now