Selenium WebDriver Java Framework Course Limited Time Offer for $20

Selenium WebDriver Java Framework Course Limited Time Offer for $20

 

Print

Delete Records in SQL Statement

When testing, sometimes, we need to delete data from the database. In SQL Server, DELETE statement removes one or more rows from a table or view.

 

When using DELETE statement, we can use conditional statement or sub queries to remove records.

(1) Delete with Where Statement

In this query below, we delete all the Products that have ProductID from 1 to 5

Use AdventureWorks2008
Go
--count number of products that have ProductID from 1 to 5
select COUNT(*) as 'Number of Products' from Production.Product where ProductID in(1,2,3,4,5)
--Delete all the Products that have ProductID from 1 to 5
Delete  from Production.Product where ProductID in(1,2,3,4,5)

(2) Delete with sub queries

In this query below, we delete all the products that does not have any transaction data

Use AdventureWorks2008
Go
--Use sub queries in Delete Statement
--This query deletes all the products that does not have any transaction data
Delete from Production.Product where ProductID in(select p.ProductID
from Production.Product p left join Production.TransactionHistory t
on p.ProductID=t.ProductID
where t.Quantity is null)