DELETE and UPDATE are one of the most powerful statements in SQL. Being responsible for production systems, a lot of times I need to make changes to data (or meta data) in production directly. Any not every system has a development environment where I could test my SQL statements. Scary !! but sadly true. So usually, I take extra care when I write or run these in a production environment. I take multiple layers of protection to undo any unintended consequences.

But the point of this post is to document a popular and very useful feature of these statements.

Let’s define a data set. Two tables T1 and T2 with just one IDENTITY column with 100 records each. T1 has records starting from 1 to 100 and T2 has records starting from 10 to 109. Records from 1 to 10 match between these two tables. Now, I want to DELETE the records in T1 that match with T2.

[sourcecode language=”sql”]

CREATE TABLE dbo.t1 (ID INT IDENTITY(1,1), col1 VARCHAR(20))
CREATE TABLE dbo.t2 (ID INT IDENTITY(10,1), col1 VARCHAR(20))
GO

INSERT INTO dbo.t1 DEFAULT VALUES
GO 100
INSERT INTO dbo.t2 DEFAULT VALUES
GO 100

SELECT * FROM dbo.t1
SELECT * FROM dbo.t2
[/sourcecode]

Since this is a simple example, there are many ways to accomplish this. But the technique I am interested in is to combine these two tables in a join and delete from one. The tables in the FROM clause, are computed first and the resultant virtual table is used to perform matching delete in T1.

[sourcecode language=”sql”]
DELETE FROM dbo.t1
FROM dbo.t1
INNER JOIN dbo.t2
ON t1.ID = t2.ID
[/sourcecode]

Example 2: Update records is T1 that match with T2. Again, because this is a simple example, there are many ways to do this. But I am interested in this particular one.

[sourcecode language=”sql”]
UPDATE dbo.t1
SET Col1 = ‘updated’
FROM dbo.t1
INNER JOIN dbo.t2
ON t1.ID = t2.ID
[/sourcecode]

Hope this helps,
_Sqltimes