bookinghasem.blogg.se

Using TOP to limit the number of rows deleted ON spqh.BusinessEntityID = sp.BusinessEntityID INNER JOIN Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesYTD > 2500000.00 GO DELETE spqh FROM DELETE FROM Sales.SalesPersonQuotaHistoryįROM Sales.SalesPersonQuotaHistory AS spqh

The first DELETE statement shows the ISO-compatible subquery solution, and the second DELETE statement shows the Transact-SQL FROM extension to join the two tables. In both examples, rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database are deleted based on the year-to-date sales stored in the SalesPerson table. The following examples show two ways to delete rows in one table based on data in another table. Using joins and subqueries to data in one table to delete rows in another table.WHERE a.BusinessEntityID = b.BusinessEntityID) ĭELETE FROM HumanResources.EmployeePayHistory

DECLARE complex_cursor CURSOR FORįROM HumanResources.EmployeePayHistory AS aįROM HumanResources.EmployeePayHistory AS b The delete operation affects only the single row currently fetched from the cursor. The following example deletes a single row from the EmployeePayHistory table in the AdventureWorks2012 database using a cursor named complex_cursor. Using a cursor to determine the row to delete.DELETE FROM Production.ProductCostHistory The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 5000.00. Using the WHERE clause to delete a set of rows i.e.The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database because a WHERE clause is not used to limit the number of rows deleted DELETE FROM Sales.SalesPersonQuotaHistory Using DELETE to discard entire rows in a table:.