CODEWITHSUNDEEP
sqlsql-serversql-delete
Manish Vij
Manish Vij

Reputation: 29

SQL server: difference between DELETE <Table> and DELETE from <Table>

SQL server:
Difference between :

Delete tblxyz where id=6

and : 

Delete from tblxyz  where id=6

Is their any difference between above queries ?

Upvotes: 3

Views: 5958

Answers (5)

TheGameiswar
TheGameiswar

Reputation: 28920

The from Clause is optional..Below is stripped down version of Syntax..

DELETE
[ TOP ( expression ) [ PERCENT ] ]
[ FROM ]
{ { table_alias

FROM An optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.

Upvotes: 0

Ali Sheikhpour
Ali Sheikhpour

Reputation: 11086

According to the MSDN the word "from" is optional. The default is not to use:

FROM

and the target table_or_view_name, or rowset_function_limited.An optional keyword that can be used between the DELETE keyword

However in MS Access (or may be some other databases) you may delete a row using delete * command, so you have to use from:

delete * from phoneBook where....

Upvotes: 0

Razieltje
Razieltje

Reputation: 357

There is no direct difference between the two statements (except that I find the "DELETE FROM" easier to read and understand)

note that ANSI does require the "FROM" keyword as stated by jarlh

see also https://msdn.microsoft.com/en-us/library/ms189835.aspx

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270391

There is no difference in your delete.

However, the FROM clause is a fully functional from clause, so you can use it with JOIN. For instance:

delete t
    from t join
         (select min(id) as minid
          from t
          group by grp
         ) tt
         on t.id = tt.minid;

This would delete the record with the minimum id for each grp value. The alias after the delete is needed to specify which table to delete from (although in this case, deleting from an aggregation result is not allowed).

Note: This query is for illustrative purposes. There is nothing wrong with the query, but it is not how I would actually write such a query in SQL Server.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

enter image description hereThere is no difference if you see the execution plan both generates delete scripts as below

DELETE [testtable]  WHERE [numbers]=@1

Upvotes: 5

Related Questions