DELETE statement

DELETE statement delete rows from temporary or basic tables, views and cursors, with the statement action in the two last cases is propagated on those basic tables, from which were extracted data into these views and cursors.

Syntax:

DELETE FROM < table name >
[WHERE < predicate >];

If the WHERE clause is absent, all rows from the table or view will be deleted (the view must be updatable). Deleting the all rows from a table in Transact-SQL can be also done (faster than a DELETE statement) by using the TRUNCATE TABLE statement.

Syntax

TRUNCATE TABLE < table name >

Some distinctions are in the realization of the TRUNCATE TABLE statement, which one should keep in mind:

1. TRUNCATE TABLE statement does not record an entry in the transaction log for each deleted row, only the page  deallocations are recorded in the transaction log.

2. Triggers do not fire.

3. This statement cannot be used on a table referenced by a FOREIGN KEY constraint.

4. The counter used by an IDENTITY for new rows is reset to the seed for the column.  

   Example 6.3.1

DELETE FROM Laptop
WHERE screen < 12;

All laptops we can delete with the query

DELETE FROM Laptop;

or

TRUNCATE TABLE Laptop;

Transact-SQL statement extends the DELETE statement over Standard introducing additional FROM clause:

FROM < table source >

This extension allows us to specify data to be deleted from the table in the first FROM clause.

This extension gives additional flexibility specifying a join that can be used instead of a subquery in the WHERE clause to identify rows to be deleted. Let’s clear the above by example.

Example 6.3.2

Using standard syntax, the task may be solved with the query:

DELETE FROM Product
WHERE type = 'pc' AND
model NOT IN (SELECT model
FROM PC
);

Note that the predicate type=‘pc’ is necessary here; otherwise printer and laptop models will be deleted.

This task can be solved through the additional FROM clause as follows:

DELETE FROM Product
FROM Product pr LEFT JOIN
PC ON pr.model = PC.model
WHERE type = 'pc' AND
PC.model IS NULL;

Here, we use outer join that results in the pc.model column contains NULL values for those PC models that are absent from the PC table, which is used for providing the criteria for the delete operation.

Suggested exercises: 5, 6, 8, 14, 16

TRUNCATE TABLE statement

As mentioned above, upon executing this statement, the counter (IDENTITY) will be reset in its initial value. Let’s check up this contention in MS SQL Server. First we shall create the table with identity column, and we shall add three rows to this table.

CREATE TABLE Truncate_test (id INT IDENTITY(5,5) PRIMARY KEY, val INT);  
GO  
INSERT INTO Truncate_test(val)   
VALUES (1),(2),(3);  
SELECT * FROM Truncate_test;  
GO

Our counter starts with identity seed of 5 and has increment of 5 also. As a result, we get

Let’s delete rows with DELETE statement, and insert these in the table once more thereafter.

DELETE FROM Truncate_test;
GO
INSERT INTO Truncate_test(val)
VALUES (1), (2), (3);
SELECT * FROM Truncate_test;
GO

As the result indicates, the counter has not been reset, and identity values proceeded to increment from 15 and so on, whereas TRUNCATE statement gives:

TRUNCATE TABLE Truncate_test;   
GO  
INSERT INTO Truncate_test(val)   
VALUES (1),(2),(3);  
SELECT * FROM Truncate_test;  
GO

By the way, SQL Standard presumes somewhat different behaviour. The standard syntax is the following

TRUNCATE TABLE < table name > [{CONTINUE IDENTITY} | {RESTART IDENTITY}]

i.e. the counter can be reset (RESTART IDENTITY option) or proceeded (CONTINUE IDENTITY option).  It should be mentioned that default value is just CONTINUE IDENTITY - the behaviour identical to use of DELETE statement (without WHERE clause).

TRUNCATE TABLE statement can’t be used if the table is being referenced by a foreign key. This standard behaviour is inherent to SQL Server. If we’ll create the following referencing table even without data at all

CREATE TABLE Trun_Ref(id INT REFERENCES Truncate_test);

TRUNCATE TABLE statement being considered above leads us to error:

Let’s check available SQL dialects for corresponding to Standard.

PostgreSQL

1. The CONTINUE IDENTITY and RESTART IDENTITY  options are supported, when unspecified  the CONTINUE IDENTITY option is implicit.
2. Truncating of rows is available in more than one table when listing them via commas in a single operator.
3. Cascade truncating can be done for tables having FOREIGN KEY constraints that reference the table:

TRUNCATE TABLE Truncate_test RESTART IDENTITY CASCADE;

Just a truncation takes place, but not only the deletion of related rows. I.e. if you add, among others, a row with NULL value in foreign key column to the child (referencing) table

INSERT INTO Trun_Ref VALUES(1), (2), (NULL);

this row will be removed also.

Oracle

1. Oracle has not autoincrement function which could be specified in a column definition. Nevertheless autoincrement behaviour can be imitated with aid of sequence. For example, theTruncate_test table mentioned above could be created in Oracle in such manner:

CREATE SEQUENCE u_seq  
    START WITH     5  
    INCREMENT BY   5;  
   /  
   CREATE TABLE Truncate_test (id INT PRIMARY KEY, val int);  
   /  
   INSERT INTO Truncate_test(id, val)   
   VALUES (u_seq.NEXTVAL,1);  
   INSERT INTO Truncate_test(id, val)   
   VALUES (u_seq.NEXTVAL,2);  
   INSERT INTO Truncate_test(id, val)   
   VALUES (u_seq.NEXTVAL,3);

2. Upon executing TRUNCATE TABLE Truncate_test command, the counter (sequence) is not being reset, and the numbering will be continued.
3. Cascade operations are not allowed, so TRUNCATE TABLE statement is not available when the table is referenced by a foreign key.

MySQL

1. The CONTINUE IDENTITY and RESTART IDENTITY options are not supported; a counter (AUTO_INCREMENT) is being reset to zero.
2. Cascade operations are similar to using DELETE statement, i.e. if foreign key has ON DELETE CASCADE option, related rows will be deleted only. This means that rows with NULL values in the foreign key column are being retained after truncating:

   TRUNCATE TABLE Truncate_test;