DATABASE

SQL Server 2008 : Working with DML Queries - Using the DELETE Statement

1/16/2011 3:22:18 PM
The DELETE statement deletes existing rows from an existing table or updateable view based on a given criteria. Let’s examine the syntax of the DELETE statement. The elements shown in Example 1 appear most commonly in the DELETE statement.
Example 1. DELETE Statement—Core Elements
DELETE
[ TOP (n) [PERCENT] ]
[FROM ]table_or_view
[FROM ...
WHERE ... ]

The table_or_view is the name of the table or view from which you are deleting rows. If you are using a view for your DELETE statement, this view must be updateable. Updateable views are based upon a single source table. You could also substitute a table-valued variable like table_or_view.

The TOP (n) [PERCENT] clause serves the same purpose as it does in the INSERT and UPDATE statements. Only the top n, or top n percent of rows will be deleted if this clause is specified.

The FROM is the filter criteria for what data should be deleted. Ensure that you specify an alias if the table or view from which you are trying to delete is mentioned more than once in the FROM clause.

The WHERE clause specifies an additional filter for data that should be deleted.

Exam Warning

Always include a WHERE clause in the DELETE statement, even if it is arbitrary. Otherwise, all rows in the source table will be deleted.


DELETE Statement Examples

Example 2 demonstrates the most common uses of the DELETE statement. Feel free to run this example against the AdventureWorks database. Performing the example yourself will give you a better understanding of how the DELETE statement is used.

Example 2. Using the DELETE Statement
USE AdventureWorks
GO

-- Create a new table holding Product Name, SubcategoryID, SubCategory, Price
and Color
SELECT Production.Product.Name as ProductName,

Production.ProductSubcategory.ProductSubcategoryID as ProductSubCategoryID,
Production.ProductSubcategory.Name as ProductSubCategory,
Production.Product.ListPrice as Price,
Production.Product.Color as Color
INTO dbo.ProductAnalysis
FROM Production.Product JOIN Production.ProductSubcategory
ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.
ProductSubcategoryID
JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID = Production.
ProductCategory.ProductCategoryID
GO

-- List the products in the ProductAnalysis table
SELECT * FROM ProductAnalysis
ORDER BY PRICE
GO

-- Delete all Products priced under $50
DELETE ProductAnalysis
WHERE Price < 50
-- List the products in the ProductAnalysis table
SELECT * FROM ProductAnalysis
ORDER BY PRICE
GO

-- List all products along with Category of Components
SELECT ProductAnalysis.ProductName, ProductCategory.Name as CategoryName,
ProductAnalysis.ProductSubCategory
FROM
ProductAnalysis JOIN Production.ProductSubcategory
ON ProductAnalysis.ProductSubcategoryID = Production.ProductSubcategory.
ProductSubcategoryID
JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID = Production.
ProductCategory.ProductCategoryID
WHERE ProductCategory.Name = 'Components'
GO

-- Delete all products in the Category 'Components' from ProductAnalysis by
joinin ProductAnalysis table with Production.ProductCategory table via the
Production.ProductSubcategory table

DELETE ProductAnalysis
FROM
ProductAnalysis JOIN Production.ProductSubcategory
ON ProductAnalysis.ProductSubcategoryID = Production.ProductSubcategory.
ProductSubcategoryID
JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID = Production.
ProductCategory.ProductCategoryID
WHERE ProductCategory.Name = 'Components'
GO

-- List all products along with Category of Components
SELECT ProductAnalysis.ProductName, ProductCategory.Name as CategoryName,
ProductAnalysis.ProductSubCategory
FROM
ProductAnalysis JOIN Production.ProductSubcategory
ON ProductAnalysis.ProductSubcategoryID = Production.ProductSubcategory.
ProductSubcategoryID
JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID = Production.
ProductCategory.ProductCategoryID
WHERE ProductCategory.Name = 'Components'
GO

Exercise . Using the DELETE Statement

In this exercise, we will practice using the DELETE statement.

Before you begin, you must have the following software installed on your computer:

  • SQL Server 2008 (a free trial is available for download)

  • AdventureWorks sample database

This exercise does not depend on you completing any previous exercises. First, we will be deleting rows in the ProductionProductReview table in the AdventureWorks database.

  1. Open SQL Server Management Studio. Click Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio.

  2. Create a new query against the AdventureWorks database.

  3. Execute the following SELECT statement. View the data and structure of the Production.ProductReview table. Take note of the number of rows in the table.

    SELECT * From Production.ProductReview
    GO
  4. Create and execute a DELETE statement to delete all reviews from reviewers whose e-mail addresses end in “@fourthcoffee.com”.

  5. Use the DELETE statement to delete all reviews from people whose e-mail addresses end in “fourthcoffee.com”.

    DELETE Production.ProductReview
    WHERE EmailAddress like '%@fourthcoffee.com'
  6. Execute the SELECT statement from step 3 again. Have records been deleted?

Next, we will be using a DELETE...FROM statement to delete rows in the Production.ProductReview table based on results of a query.

  1. Create a new query against the AdventureWorks database.

  2. Find out the relationships between Production.ProductReview and Production.Product. You may find it helpful to create a diagram, mapping these relationships.

  3. Create and execute a DELETE statement to delete all reviews for the product named HL Mountain Pedal. How many rows were affected?

  4. Use the DELETE statement to delete all reviews for the HL Mountain Pedal Product.

    DELETE Production.ProductReview
    FROM Production.ProductReview JOIN Production.Product
    ON ProductReview.ProductID = Product.ProductID
    WHERE Product.Name = 'HL Mountain
Other  
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 2) - Data Mining Predictions Using DMX
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 1) - Data Mining Modeling Using DMX
  •  SQL Server 2008 : Working with DML Queries - Using the UPDATE Statement (part 2)
  •  SQL Server 2008 : Working with DML Queries - Using the UPDATE Statement (part 1)
  •  Azure Programming Considerations
  •  Programming with SQL Azure : Record Navigation in WCF Data Services
  •  Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 2) - TRY...CATCH blocks cannot catch all errors
  •  Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 1) - Re-throwing errors
  •  SQL Server 2008 : Working with DML Queries - Using the INSERT Statement (part 2)
  •  SQL Server 2008 : Working with DML Queries - Using the INSERT Statement (part 1) - Using the INSERT Statement with the VALUES Clause
  •  
    Most View
    Samsung Galaxy Camera - An Android Compact 21X Shooter (Part 1)
    Microsoft Enterprise Library : A Cache Advance for Your Applications - How Do I Use the Caching Block (part 1) - Adding Items to and Retrieving Items from the Cache
    Windows Server 2012 : Implementing Group Policy preferences (part 3) - Understanding preferences - Item-level targeting, Configuring a preference item
    Adobe After Effects CS5 : Dynamic Range: Bit Depth and Film (part 3) - Video Gamma Space, Linearized Working Space
    ASP.NET 3.5 : Caching ASP.NET Pages (part 4) - Advanced Caching Features
    Hot Technology Gadgets – June 2013
    Acer Aspire R7 - A Flexible Form Factor With A Reasonable Price (Part 2)
    Nikon Coolpix A – An Appealing Camera For Sharp Images (Part 1)
    The Oppo Find 5 - Built Like A Boss
    Smashing Html5 : Web Navigation Concepts (part 1) - Designer navigation and user navigation, Global navigation
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
    Top 10
    The BMW X4 – Strong Performance (Part 3)
    The BMW X4 – Strong Performance (Part 2)
    The BMW X4 – Strong Performance (Part 1)
    The BMW X5 25d – Top Truck
    The Champion – Widebody Gc8 Built For All The Right Reasons (Part 2)
    The FPV GT-F – This Is The End (Part 2)
    The FPV GT-F – This Is The End (Part 1)
    Teenage Kicks - Britain's Youngest Hot Rodders (Part 3)
    Teenage Kicks - Britain's Youngest Hot Rodders (Part 2)
    Teenage Kicks - Britain's Youngest Hot Rodders (Part 1)