Delete statement in SQL Server [closed]

I saw a delete statement written in below format and I need help in understanding it.

create table t1 (id int);
create table t2 (id int);

insert into t1 values(1);
insert into t1 values(2);
insert into t2 values(1);

delete t1
from t2
where t1.id = t2.id;

I tried above in online SQL Server database but script is failing. I am not sure how similar script is running in production environment.

Answer

The DELETE statement has the following syntax:

[ WITH <common_table_expression> [ ,...n ] ]  
DELETE   
    [ TOP ( expression ) [ PERCENT ] ]   
    [ FROM ]   
    { { table_alias  
      | <object>   
      | rowset_function_limited   
      [ WITH ( table_hint_limited [ ...n ] ) ] }   
      | @table_variable  
    }  
    [ <OUTPUT Clause> ]  
    [ FROM table_source [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                   { { [ GLOBAL ] cursor_name }   
                       | cursor_variable_name   
                   }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <Query Hint> [ ,...n ] ) ]   
[; ]

When pinpointing the parts from your example, you get:

DELETE   
    { 
        <object>   
    }  
    [ FROM table_source [ ,...n ] ]   
    [ WHERE { <search_condition>    
            }   
    ]

Being <object> the following:

<object> ::=  
{   
    [ server_name.database_name.schema_name.   
      | database_name. [ schema_name ] .   
      | schema_name.  
    ]  
    table_or_view_name   
}

So what your DELETE statement is doing is joining table t1 with t2 via a condition on the WHERE clause, and then deleting all matching records from table t1.

Regular SQL syntax requires is to filter your rows from a WHERE clause:

DELETE FROM <table>
WHERE <condition>

Transact-SQL has a few extensions to this base format. First, the FROM is optional, so you can write:

DELETE <table>
WHERE <condition>

and second, you can filter rows from your deleting table through joining against other table sources (which is pretty common), so they give you the option to move some of your WHERE conditions to an optional, new FROM <table_source> clause.

DELETE <table>
FROM <table_source>
WHERE <condition>

The FROM from the first point is still optional, you can also write:

DELETE FROM <table>
FROM <table_source>
WHERE <condition>

And you can put your joining conditions on either a JOIN:

DELETE a
FROM t1 as a INNER JOIN t2 as b on a.id = b.id

or the WHERE:

DELETE t1
FROM t2
WHERE t1.id = t2.id

But I suggest using the former, since it’s easier to read for bigger queries.

Advertisement

Answer