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.
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.
Some distinctions are in the realization of the TRUNCATE TABLE statement, which one should keep in mind:
Needed to delete from the Laptop table all the laptops with the screen size less than 12 in.
All laptops we can delete with the query
Transact-SQL statement extends the DELETE statement over Standard introducing additional FROM clause:
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.
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:
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:
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.