title: "DELETE (Transact-SQL) | Microsoft Docs"

ms.custom: ""

ms.prod: "sql-non-specified"

ms.reviewer: ""

ms.suite: ""

```

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. For more information, see [WITH common_table_expression (Transact-SQL)](../../t-sql/queries/with-common-table-expression-transact-sql.md).

The alias specified in the FROM *table_source* clause representing the table or view from which the rows are to be deleted.

*server_name*

The name of the server (using a linked server name or the [OPENDATASOURCE](../../t-sql/functions/opendatasource-transact-sql.md) 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.

The view referenced by *table_or_view_name* must be updatable and reference exactly one base table in the FROM clause of the view definition. For more information about updatable views, see [CREATE VIEW (Transact-SQL)](../../t-sql/statements/create-view-transact-sql.md).

*rowset_function_limited*

Either the [OPENQUERY](../../t-sql/functions/openquery-transact-sql.md) or [OPENROWSET](../../t-sql/functions/openrowset-transact-sql.md) function, subject to provider capabilities.

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)](../../t-sql/queries/hints-transact-sql-table.md).

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)](../../t-sql/queries/output-clause-transact-sql.md).

FROM *table_source*

This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

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

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)](../../t-sql/queries/search-condition-transact-sql.md).

CURRENT OF

*cursor_variable_name*

The name of a cursor variable. The cursor variable must reference a cursor that allows updates.

Keywords that indicate which optimizer hints are used to customize the way the [!INCLUDE[ssDE](../../includes/ssde-md.md)] processes the statement. For more information, see [Query Hints (Transact-SQL)](../../t-sql/queries/hints-transact-sql-query.md).

Examples in this section demonstrate how to limit the number of rows that will be deleted.

The following example deletes all rows from the `ProductCostHistory` table in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database in which the value in the `StandardCost` column is more than `1000.00`.

The following example shows a more complex WHERE clause. The WHERE clause defines two conditions that must be met to determine the rows to delete. The value in the `StandardCost` column must be between `12.00` and `14.00` and the value in the column `SellEndDate` must be null. The example also prints the value from the **@@ROWCOUNT** function to return the number of deleted rows.

```

```

The following example deletes a single row from the `EmployeePayHistory` table in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database using a cursor named `my`_`cursor`. The delete operation affects only the single row currently fetched from the cursor.

```

```

The following examples show two ways to delete rows in one table based on data in another table. In both examples, rows from the `SalesPersonQuotaHistory` table in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database are deleted based on the year-to-date sales stored in the `SalesPerson` table. The first `DELETE` statement shows the ISO-compatible subquery solution, and the second `DELETE` statement shows the [!INCLUDE[tsql](../../includes/tsql-md.md)] FROM extension to join the two tables.

```

```

```

When a TOP (*n*) clause is used with DELETE, the delete operation is performed on a random selection of *n* number of rows. The following example deletes `20` random rows from the `PurchaseOrderDetail` table in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database that have due dates that are earlier than July 1, 2006.

```

Examples in this section demonstrate how to delete rows from a remote table by using a [linked server](../../relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql.md) or a [rowset function](../../t-sql/functions/rowset-functions-transact-sql.md) to reference the remote table. A remote table exists on a different server or instance of SQL Server.

The following example deletes rows from a remote table. The example begins by creating a link to the remote data source by using [sp_addlinkedserver](../../relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql.md). The linked server name, `MyLinkServer`, is then specified as part of the four-part object name in the form *server.catalog.schema.object*.

```

```

The following example deletes rows from a remote table by specifying the [OPENQUERY](../../t-sql/functions/openquery-transact-sql.md) rowset function. The linked server name created in the previous example is used in this example.

```

```

The following example deletes rows from a remote table by specifying the [OPENDATASOURCE](../../t-sql/functions/opendatasource-transact-sql.md) rowset function. Specify a valid server name for the data source by using the format *server_name* or *server_name\instance_name*.

```

The following example shows how to save the results of a `DELETE` statement to a table variable in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database.

```

```

The following example deletes rows in the `ProductProductPhoto` table in the [!INCLUDE[ssSampleDBnormal](../../includes/sssampledbnormal-md.md)] database based on search criteria defined in the `FROM` clause of the `DELETE` statement. The `OUTPUT` clause returns columns from the table being deleted, `DELETED.ProductID`, `DELETED.ProductPhotoID`, and columns from the `Product` table. This is used in the `FROM` clause to specify the rows to delete.

```

The following example deletes all rows from the `Table1` table because a WHERE clause is not used to limit the number of rows deleted.

```

```

The following example deletes all rows from the `Table1` table that have a value greater than 1000.00 in the `StandardCost` column.

```

```

The following example uses a label with the DELETE statement.

```

```

This query shows the basic syntax for using a query join hint with the DELETE statement. For more information on join hints and how to use the OPTION clause, see [OPTION (SQL Server PDW)](http://msdn.microsoft.com/en-us/72bbce98-305b-42fa-a19f-d89620621ecc).

```