Sandline Discovery - Insight, Ideas and eDiscovery
From time to time, we need to look under the hood of our tools and do things directly to the underlying databases, using, of course, the Structured Query Language, SQL.
SQL is dangerous, in a great power-responsibility way. Updating data in a SQL database has no "undo" option. If you are using Microsoft's SQL Server Management studio, for example, and you run a command like this:
update documents set responsiveness = null
All your responsiveness coding will be unceremoniously gone in the blink of an eye. One easy way to bring this disaster upon yourself is to start typing out the query and get distracted before you finish. What you meant to write was this:
update documents set responsiveness = null where doc in (docs_to_update) (I forgot that part)
So, recoil from the bare word "update". When writing an update statement, begin with a comment marker. Two dashes make the remainder of the line a comment, so if you run this SQL, nothing happens:
--update documents set responsiveness = null
When you are ready to actually run it, SQL Server management studio allows you to choose the portion you want and execute just the selection. To actually update, select the bit you want and click go, omitting the two dashes at the beginning:
--update documents set responsiveness = null where doc in (docs_to_update)
I usually go a bit further and write something like this:
select doc_id, responsiveness --update d set responsiveness = null
from documents as d where doc in (docs_to_update)
I first run the query as a select statement, that is, without selecting anything. It then shows the documents to be updated. Another trick is that this aliases the "documents" table as letter "d". Thus, if I accidentally select the wrong portion:
select doc_id, responsiveness
--update d set responsiveness = null
from documents d where doc in (docs_to_update)
Again, nothing happens.
Tricks like this help, but are hardly bulletproof. So remember: only you can prevent SQL disasters. Know what the destructive operation are and before you do them, stop, take a deep breath and think about it. No matter how bad your day is, a malformed SQL statement will always make it worse.
Written by Joe Ulfers
We just sent you an email. Please click the link in the email to confirm your subscription!