SQL data manipulation language

Data modification statements

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 (T-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.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

Needed to delete from the Laptop table all the laptops with the screen size less than 12 in.

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

Let us need to delete the PC models from the Product table that have not corresponding rows in the PC table.

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.