If you're a DBA, especially one that has to deal with an ERP system that has no concept of silly things like "transactions", then you've probably got a collection of scripts to update and fix data as necessary.
And you probably (hopefully) run a SELECT before actually changing data, to visually confirm what will happen. You might do this by either having both a SELECT and UPDATE query in the script, or by commenting out the SELECT or UPDATE portions of the script as needed, or some other technique.
Here's an approach I've used for a few years now, which I've never seen mentioned elsewhere. This fictitious query would change the sales rep to 'Unassigned' for any customers with no orders in the past year.
SELECT cust.CustomerNumber, cust.CustomerName, cust.SalesRep /* UPDATE cust SET SalesRep = 'Unassigned' --*/ FROM Customers cust LEFT OUTER JOIN Orders ord ON cust.CustomerNumber = ord.CustomerNumber AND ord.OrderDate >= DATEADD(year, -1, GETDATE()) WHERE ord.OrderNo IS NULL
So what's going on? If you run the whole script, you get a SELECT query, since the UPDATE is commented out. If you highlight the line with UPDATE through the end of the query in order to execute only those lines (a nice feature of Management Studio), the script runs as an UPDATE statement. The magic happens in
--*/. If you run the whole script, the
-- is ignored as being inside the multi-line comment. If you run everything from
UPDATE onward, the
-- comments out the
*/, so there's no syntax error.
This also works with INSERT and DELETE statements; for an INSERT, you'll put the comment block before the SELECT list rather than after.
And if you want to be even more careful, replace
BEGIN TRANSACTION; UPDATE.
Best of all, you don't have to maintain two identical queries, you don't have to worry about commenting/uncommenting the SELECT or UPDATE depending on what you need, and you don't have to worry about changing data if you accidentally run the whole script. Neat, huh?