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

Question

I am 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?).

Solution 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 ...

Solution 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.

Solution 3

From the Microsoft SQL Server documentation, FROM is optional.

Comments

[Hamish Grubijan]

please elaborate on the last example you have listed so far. Would that delete all rows in some_table phone_book is not null, plus the other condition? I guess I do not understand the advantage of this special case. Why would not it simply be delete from some_table where ... ?

[Lucero]

@Hamish, please have a look at the three links I posted in the first line. My some_table is the source table which can be used to get values which are matched in the WHERE clause afterwards to filter the items to be deleted from phone_book. It could also be written as: DELETE FROM phone_book FROM some_table WHERE ... - but as I wrote, only the first FROM is optional in this case.

[Hamish Grubijan]

thanks ... would you please elaborate? By the way, "don't use generated code" would require several man-years to undo :) As I said, I am new to SQL. Please explain why you came up with these rules.

[Lucero]

Linking to a SQL Server 2000 reference and skipping the other vendors doesn't seem like a complete answer?

[Lucero]

@Hamish, I know who Joel is, a generally nice guy and great writer, and I've been a customer of his company since around 2002. Still I don't think that this answer is actually answering your question.