How
to delete rows with no where clause
The following example deletes all rows from the Person.Person the
table in the AdventureWorks2014 database. There is no restriction enforced on
the SQL delete statement using a WHERE clause.
USE Adventureworks2014;
GO
DELETE FROM [Person].[Person];
How
to delete rows using Top with where clause
The following example
deletes 50 random rows from the Person.Person table in the AdventureWorks2014 database. The value in
the BusinessEntityID must be in between 30,000 and 40,000
USE Adventureworks2014;
GO
DELETE TOP(50) FROM [Person].[Person]
WHERE BusinessEntityID between 30000 and 40000
How
to delete duplicate rows
In the real-world, we tend to gather data from
different sources; it’s not uncommon to have duplicate records. One approach to
the duplicate problem is first to identify where the duplicates have occurred.
And run a select query on those columns.
EATE TABLE tb_spaceused
(database_name NVARCHAR(128),
database_size VARCHAR(18),
[unallocated space] VARCHAR(18),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
);
INSERT INTO tb_spaceused
EXEC sp_msforeachdb
@command1 = "use ? exec sp_spaceused @oneresultset = 1";
SELECT *
FROM tb_spaceused
order by database_name
The following example
uses the PARTITION BY argument to partition the query result set by all the
columns of tb_spaceused table. The Row_Number (), a window function, which
means it operates over an ordered set. The ORDER BY clause specified in the
OVER clause orders the rows in each partition by the entire columns
tb_spaceused table.
WITH CTE
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY database_name,
database_size,
[unallocated space],
reserved,
data,
index_size,
unused
ORDER BY database_name
) AS Row_Num
FROM tb_spaceused)
SELECT *
FROM CTE
WHERE Row_Num <> 1;
Replacing the Select statement with a Delete
removes all the duplicates of the table.
WITH CTE
AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY database_name,
database_size,
[unallocated space],
reserved,
data,
index_size,
unused
ORDER BY database_name
) AS Row_Num
FROM tb_spaceused)
--SELECT *
--FROM CTE
--WHERE Row_Num <> 1;
DELETE FROM CTE
WHERE Row_Num <> 1;
How
to delete rows using SQL sub-queries
In the following example, the rows in one table are deleted
based on data in another table. In the examples, the rows from the SalesPersonQuotaHistory table
are deleted based on the SalesYTD column
of the SalesPerson table.
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(
SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 4900000.00
);
GO
How
to delete rows using SQL Joins
In this section, we will use the SQL Delete statement to delete
the data from the Adeventureworks2014 database. Deleting data, at first sight,
sound trivial, but once we get into a large database design things might not be
same and easy anymore.
In many cases, the tables are related via a primary and foreign
key relationship. In the following example, we can see a use of joins to delete
the data from the Sales.SalesPersonQuotaHistory.
DELETE sq
FROM Sales.SalesPersonQuotaHistory sq
INNER JOIN Sales.SalesPerson sp ON sq.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 4500000.00;
GO
How
to delete rows from a remote table using linked servers and OpenQuery
The following example uses the SQL delete statement to delete
rows from a remote table using the linked server named, hqdbt01. Then query the
remote table using four-part object naming convention to delete the rows from
the remote table
DELETE
FROM [hqdbt01].AdventureWorks2014.[HumanResources].[Shift]
WHERE ShiftID = 2;
The following example,
the remote table is queried by specifying the OPENQUERY rowset function along with the delete command.
DELETE OPENQUERY (hqdbt01, 'SELECT *
FROM AdventureWorks2014.HumanResources.Department
WHERE DepartmentID = 18');
