Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The BOL and many other sources point out:

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?

share|improve this question
up vote 14 down vote accepted

Here is one case where it would be hard to write it without:

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
    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);
share|improve this answer
Thanks Aaron. I don't think it should be mandatory, just was asking for a situation where without it the sentence will not work. By your samples I get to the conclusion that if mytable AS x is used then FROM will be mandatory. Correct? – Yaroslav Jan 22 '14 at 15:57
@Yaroslav no, the AS has nothing to do with it - it's the fact that you are deleting from a join or more complex expression than just a single table. I've updated the example to show this. – Aaron Bertrand Jan 22 '14 at 15:59
Ouch! Now I got it. – Yaroslav Jan 22 '14 at 16:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.