DELETE (TSQL Statement)

delete records from the specified table.

Syntax

DELETE [ FROM ] { <table_qname> | table_alias }
[ FROM { <table_expression> } [ ,...n ] ]
[ WHERE condition ]

<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name

Arguments

<table_qname>
is the qualified or unqualified name of the target table to delete.
table_alias
alias name specified in the FROM clause for the target table to delete.
FROM { <table_expression> } [ ,…n ]
specifies the FROM clause of the underlying SELECT command, which produces the rows from which the target table will be deleted.
WHERE condition;
specifies the WHERE clause of the underlying SELECT command.

Remarks

The function @@ROWCOUNT returns the number of records deleted.

To delete all records from a table, use TRUNCATE TABLE statement, as it is faster.

Internally, the DELETE statement works very much like the UPDATE statement, but deletes the matching records instead of updating them:

For example:

DELETE mytable
FROM another_table t
WHERE a = t.col1;

As the target table mytable doesn’t appear in the FROM clause, RSQL automatically adds it in the list at first position.

DELETE mytable
FROM mytable, another_table t
WHERE a = t.col1;

The underlying SELECT statement for this DELETE statement is:

SELECT *
FROM mytable, another_table t
WHERE a = t.col1;

Permissions

The user must have delete permission on the target table, and select permission on the referenced tables.

Examples

IF OBJECT_ID ('mytable', 'U') IS NOT NULL
    DROP TABLE mytable;

CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL);
GO

INSERT INTO mytable VALUES (11, 'Row #1'), (12, 'Row #2'), (13, 'Row #3');

DELETE mytable
WHERE a <= 12;

SELECT * FROM mytable;

The result is:

a          |b                                  |
-----------+-----------------------------------+
         13|Row #3                             |

Examples: Delete Table from Another Table

Internally, the DELETE statement is in fact a SELECT statement. Then, for each row of the result, the corresponding row of the target table is deleted.

IF OBJECT_ID ('mytable', 'U') IS NOT NULL
    DROP TABLE mytable;

IF OBJECT_ID ('another_table', 'U') IS NOT NULL
    DROP TABLE another_table;

CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL);

CREATE TABLE another_table (col1 INT NOT NULL);
GO

INSERT INTO mytable(a, b) VALUES
(11, 'Row #1'), (12, 'Row #2'), (13, 'Row #3');

INSERT INTO another_table VALUES
(11), (12), (12), (14);

DELETE mytable
FROM another_table t
WHERE a = t.col1;

/* same as above, but mytable is explicitly put in the FROM clause
DELETE mytable
FROM mytable, another_table t
WHERE a = t.col1;
*/

SELECT * FROM mytable;

The result is:

a          |b                                  |
-----------+-----------------------------------+
         13|Row #3                             |

Note that two records from another_table match the record from mytable for a == 12, but this is no problem as multiple deletions of the same record is allowed.

Examples: with JOIN Syntax

All the syntax for the FROM and WHERE clause of the SELECT statement are allowed (JOIN ... ON, LEFT JOIN ... ON, etc).

IF OBJECT_ID ('mytable', 'U') IS NOT NULL
    DROP TABLE mytable;

IF OBJECT_ID ('another_table', 'U') IS NOT NULL
    DROP TABLE another_table;

CREATE TABLE mytable (a INT NOT NULL PRIMARY KEY, b VARCHAR(50) NULL);

CREATE TABLE another_table (col1 INT NOT NULL);
GO

INSERT INTO mytable(a, b) VALUES
(11, 'Row #1'), (12, 'Row #2'), (13, 'Row #3');

INSERT INTO another_table VALUES
(11), (12), (12), (14);

DELETE mt
FROM mytable mt JOIN another_table t ON a = t.col1

SELECT * FROM mytable;

The result is:

a          |b                                  |
-----------+-----------------------------------+
         13|Row #3                             |