TSQL – Self Update

This morning I needed to compose a very simple SQL routine and it took me around five compiles until I got the syntax right.  All I needed to do was transfer an active status from one entity (in my case a computer) to another.  I decided to implement this by updating the same table in which I was selecting.  I think the syntax associated with “self update” type queries is tricky so this post is so I personally don’t lose this code snippet and future cycles.  I hope it might help you as well.

This example transfers the status of the source computer to the destination computer.  Nothing fancy…

DECLARE @SourceID INT; SET @SourceID = 2
DECLARE @DestinationID INT; SET @DestinationID = 1

DECLARE @Computer TABLE (ComputerID INT, StatusID INT)

INSERT INTO @Computer(ComputerID, StatusID)
SELECT @DestinationID, 1 UNION SELECT @SourceID, 2

SELECT * FROM @Computer

UPDATE A
SET A.StatusID = B.StatusID
FROM @Computer A, @Computer B
WHERE B.ComputerID = @SourceID
AND A.ComputerID = @DestinationID

SELECT * FROM @Computer

(2 row(s) affected)
ComputerID  StatusID
----------- -----------
1           1
2           2

(2 row(s) affected)

(1 row(s) affected)

ComputerID  StatusID
----------- -----------
1           2
2           2

(2 row(s) affected)