GAPTHEGURU

Geek with special skills

syntax and arguments in T-SQL for SQL Server 2008 r2

Topic link iconSyntax


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

<object> ::=
{
    [ server_name.database_name.schema_name.
      | database_name. [ schema_name ] .
      | schema_name.
    ]
    table_or_view_name
}
WITH <common_table_expression>
Specifies the temporary named result set, also known as common table expression, defined within the scope of the DELETE statement. The result set is derived from a SELECT statement.Common table expressions can also be used with the SELECT, INSERT, UPDATE, and CREATE VIEW statements.

TOP (expression) [ PERCENT ]
Specifies the number or percent of random rows that will be deleted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.Parentheses delimiting expression in TOP are required in INSERT, UPDATE, and DELETE statements.

FROM
Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited. 
server_name
Is the name of the server (using a linked server name or the OPENDATASOURCE function as the server name) on which the table or view is located. If server_name is specified, database_name and schema_name are required. 
database_name
Is the name of the database. 
schema_name
Is the name of the schema to which the table or view belongs. 
table_or view_name
Is the name of the table or view from which the rows are to be removed.A table variable, within its scope, also can be used as a table source in a DELETE statement.

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

rowset_function_limited
Is either the OPENQUERY or OPENROWSET function, subject to provider capabilities. For more information about the capabilities required by the provider, see UPDATE and DELETE Requirements for OLE DB Providers
WITH ( <table_hint_limited> [… n] )
Specifies one or more table hints that are allowed for a target table. The WITH keyword and the parentheses are required. NOLOCK and READUNCOMMITTED are not allowed. For more information about table hints, see Table Hints (Transact-SQL)
<OUTPUT_Clause>
Returns deleted rows, or expressions based on them, as part of the DELETE operation. The OUTPUT clause is not supported in any DML statements targeting views or remote tables. For more information, see OUTPUT Clause (Transact-SQL)
FROM <table_source>
Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause.This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

For more information, see FROM (Transact-SQL).

WHERE
Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table.There are two forms of delete operations based on what is specified in the WHERE clause:

  • Searched deletes specify a search condition to qualify the rows to delete. For example, WHERE column_name = value.
  • Positioned deletes use the CURRENT OF clause to specify a cursor. The delete operation occurs at the current position of the cursor. This can be more accurate than a searched DELETE statement that uses a WHERE search_condition clause to qualify the rows to be deleted. A searched DELETE statement deletes multiple rows if the search condition does not uniquely identify a single row.
<search_condition>
Specifies the restricting conditions for the rows to be deleted. There is no limit to the number of predicates that can be included in a search condition. For more information, see Search Condition (Transact-SQL)
CURRENT OF
Specifies that the DELETE is performed at the current position of the specified cursor. 
GLOBAL
Specifies that cursor_name refers to a global cursor. 
cursor_name
Is the name of the open cursor from which the fetch is made. If both a global and a local cursor with the name cursor_name exist, this argument refers to the global cursor if GLOBAL is specified; otherwise, it refers to the local cursor. The cursor must allow updates. 
cursor_variable_name
Is the name of a cursor variable. The cursor variable must reference a cursor that allows updates. 
OPTION ( <query_hint> [ ,… n] )
Are keywords that indicate that optimizer hints are used to customize the way the Database Engine processes the statement. For more information, see Query Hints (Transact-SQL)
Advertisements

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: