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 underlyingSELECT
command, which produces the rows from which the target table will be deleted. WHERE
condition;- specifies the
WHERE
clause of the underlyingSELECT
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:
- An underlying
SELECT
statement is created, made of theFROM
andWHERE
clauses of theDELETE
statement. - If the target table doesn’t appear in the
FROM
clause, it is automatically added as first element in this list of tables. - This underlying
SELECT
statement is run, and for each record produced, the corresponding record in the target table is deleted.
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 |