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


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