September 24, 2012

Delete using another Table

In this example I create a temp table on AdventureWorks2012 Database, and first I load all the data in HumanResources.Employee to the temp table. Then I delete data using HumanResources.Employee. This command will delete all the records in Tmp which has Gender = ‘M’ in HumanResources.Employee table.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT *
INTO Tmp
FROM [HumanResources].[Employee]
GO

--DELETE Using another table
DELETE Tmp
FROM [HumanResources].[Employee] AS HE, Tmp
WHERE Tmp.BusinessEntityID = HE.BusinessEntityID
AND HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

You can use inner or outer join to combine those data.


USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT *
INTO Tmp
FROM [HumanResources].[Employee]
GO

--DELETE Using another table
DELETE Tmp
FROM [HumanResources].[Employee] AS HE
INNER JOIN Tmp
ON Tmp.BusinessEntityID = HE.BusinessEntityID
WHERE HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

Both of those statements gives same results. It will delete all the male employees.

1 comment: