SQL: Safe Update with Select
Author: jason
Date: 2014-01-22 11:23:57
Category: Musing

This might seem minor, but I like to nest my UPDATE statements inside SELECT statements. This reassures me that the update should go off without a hitch since the select statement targeted exactly what I wanted.

The UPDATE is commented out in the code below, but its set inside the SELECT. The intent is that if the SELECT returns the right rows then the UPDATE will too. I added the existing value of the column as the last AND statement. This will help if we ever want to revert the UPDATE statement by listing the old value.

Step 1: Run the SELECT statement to ensure the rows returned are what are desired




Step 2: Highlight from the UPDATE down and execute




Step 3: Highlight everything but the last AND to see the results




Code

SELECT s.id, s.myValue
--UPDATE s SET s.myValue = 'And more stuff'
FROM mytest.dbo.mySecond S WHERE
s.id = 1
AND s.myValue = 'stuff'




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009