In SQL syntax, is 'from' in 'delete from' optional if you plan to use 'where'?

Question

I'm new to SQL. We have some code that should work on SQL Server 2005/2008, Oracle 10 as well as Sybase.

I was writing a script to try to figure out which tables a given stored procedure modifies (but does not drop), e.g insert, update and delete.

The delete one turned out being puzzling - sometimes I see statements like:

delete phone_book where ... 

as opposed to:

delete from phone_book where ...

So ... is the from keyword truly optional in this case? Does this cause any problems? Is it just a bad style, or does it not matter?

I have not found a reference to T-SQL that would make from optional. I suppose that this is what would unify all 3 vendors I mentioned above.

Questions/comments/links are welcomed (or is it welcome?).

Answer 1

At this place the FROM is optional (SQL Server, Oracle, Sybase).

However, there are subtle differences: Oracle for instance allows assigning an alias to the table name, where SQL Server doesn't; and other things are also a little bit different.

Also note that your FROM sample is differnet from the following where it is mandatory:

DELETE phone_book FROM some_table WHERE ...

Answer 2

Short Answer: Luceros answer is correct: it is optional

I have to maintain sql and adapt it between sql-server and Oracle. Here are some rules:

1. Write Scripts manually, don't use generated code.
2. Always use INSERT INTO.
3. Always DELETE -- without FROM.
4. Do not use " - quoted identifier.
5. Remove all [ ] and dbo. (Schema names)
6. Attention when you see DELETE ... FROM ...
7. Attention when you see UPDATE ... FROM ...

8. ORACLE Select statements need a from clause you can use from DUAL

   1. OK you can script your objects and edit them in a standard way
      • USE [Current_DB] -- you don't want a reference to your test database go into production script
      • SET ANSI_NULLS ON -- decide once which settings to use -- don't switch on and off
      • SET QUOTED_IDENTIFIER ON -- quoted identifiers are case-sensitive.
   2. INSERT INTO is required by Oracle.
   3. That is my personal style don't use optional keyword, learn the defaults
   4. You have to quote an identifier, if you use one of ORACLES reserved keywords as column name, we entered that pitfall and in the long run it would have been better to rename the column on the sql-Server side.
   5. Oracle doesn't use these.
   6. Oracle doesn't support this syntax.
   7. Oracle doesn't support this syntax.

Answer 3

From the Microsoft SQL Server documentation, FROM is optional.