loading..
Русский    English
05:45

DELETE statement page 1

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:

  1. DELETE FROM < TABLE name >
  2. [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

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

  1. DELETE FROM Laptop
  2. WHERE screen < 12;

All laptops we can delete with the query

  1. DELETE FROM Laptop;

or

  1. TRUNCATE TABLE Laptop;

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

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

  1. DELETE FROM Product
  2. WHERE type = 'pc' AND
  3. model NOT IN (SELECT model
  4. FROM PC
  5. );

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:

  1. DELETE FROM Product
  2. FROM Product pr LEFT JOIN
  3. PC ON pr.model = PC.model
  4. WHERE type = 'pc' AND
  5. 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

Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.