Thursday, May 5, 2022

SQL Important Queries

 

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');

 

 

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...