patternsqlModerate
Use of FROM keyword on DELETE statement
Viewed 0 times
deletestatementkeywordfromuse
Problem
The BOL and many other sources point out:
FROM
Is an optional keyword that can be used between the DELETE keyword and the target
table_or_view_name, or rowset_function_limited.
I'm used to write
FROM
Is an optional keyword that can be used between the DELETE keyword and the target
table_or_view_name, or rowset_function_limited.
I'm used to write
DELETE sentences without the FROM. I've made some searches but can't find where the FROM is mandatory. Can anyone point out a situation where it should be mandatory? Or is always optional?Solution
Here is one case where it would be hard to write it without:
Or:
The
This is similar to
DELETE /* FROM */ t1 -- this FROM is optional
FROM dbo.t1 -- this FROM is mandatory
INNER JOIN dbo.t2 AS t2
ON t1.key = t2.key
WHERE t2.key IN (1,2,3);Or:
DELETE /* FROM */ t1 -- this FROM is optional
FROM dbo.t1 -- this FROM is mandatory
WHERE EXISTS
(
SELECT 1 FROM dbo.t2
WHERE key IN (1,2,3)
AND key = t1.key
);The
FROM in the initial spot (e.g. DELETE FROM t1 FROM dbo.t1 ...) is optional, just as Books Online says. Why do you think it ever should be mandatory? Borrowing from Thomas' answer, which he gracefully deleted, the following are identical:DELETE Sales.SalesOrderDetail WHERE SalesOrderID = -1;
DELETE FROM Sales.SalesOrderDetail WHERE SalesOrderID = -1;This is similar to
INSERT - the following are absolutely 100% equivalent, and you don't ever have to write INTO:INSERT dbo.t1(key) VALUES(1),(2),(3);
INSERT INTO dbo.t1(key) VALUES(1),(2),(3);Code Snippets
DELETE /* FROM */ t1 -- this FROM is optional
FROM dbo.t1 -- this FROM is mandatory
INNER JOIN dbo.t2 AS t2
ON t1.key = t2.key
WHERE t2.key IN (1,2,3);DELETE /* FROM */ t1 -- this FROM is optional
FROM dbo.t1 -- this FROM is mandatory
WHERE EXISTS
(
SELECT 1 FROM dbo.t2
WHERE key IN (1,2,3)
AND key = t1.key
);DELETE Sales.SalesOrderDetail WHERE SalesOrderID = -1;
DELETE FROM Sales.SalesOrderDetail WHERE SalesOrderID = -1;INSERT dbo.t1(key) VALUES(1),(2),(3);
INSERT INTO dbo.t1(key) VALUES(1),(2),(3);Context
StackExchange Database Administrators Q#57282, answer score: 14
Revisions (0)
No revisions yet.