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

sql tsql delete-row sql-delete
Hamish Grubijan picture Hamish Grubijan · Dec 19, 2010 · Viewed 11.5k times · Source

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

Answer

Lucero picture Lucero · Dec 19, 2010

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

Related questions

Delete duplicate records in SQL Server?

Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field. EmployeeName ------------ Anand Anand Anil Dipak Anil Dipak Dipak Anil Using one query, I want to delete the records which are …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More