Getting rid of data

In previous posts, we’ve talked about SELECTing data, INSERTing data, and UPDATEing data. Now, what do we do if we want to just get rid of the data?

Now, in most scenarios, you really don’t want to get rid of data. As a data professional, it is definitely not a good idea to get rid of data that is in the database, especially if you’re in the Production environment. This protects you and the business for reasons such as corporate governance, regulatory compliance, and legal safeguards.

In the best of all perfect worlds, to mark a record as deleted, you would do something similar to the following:

UPDATE [SampleTable] SET IsDeleted = 1 WHERE FirstName = ‘Jill’ AND LastName = ‘Smith’;

Yes, you read that right. You should use the UPDATE statement to mark a record as being deleted. The above query will set the IsDeleted field to a value of TRUE (a value of 0 would be FALSE ) where the person’s name is Jill Smith.

Now, as always, there are exceptions to that rule. Sometimes, you can truly delete the data from the database.

Doing that is actually fairly simple.

DELETE FROM [SampleTable] WHERE FirstName = ‘Jill’ AND LastName = ‘Smith’;

Hopefully, you’ll notice how similar it is to the SELECT query. The problem is that it is an extremely powerful, and destructive, force. A SELECT statement just reads data from the database, but a DELETE statement can get rid of all of the data that matches what matches the criteria shown in the WHERE clause.

So, just as a word of caution, when you actually attempt to DELETE data, use the old carpenter’s idiom: Measure twice, Cut once. In other words, make doubly sure that you really want to delete everything that you have in the WHERE clause.

So, next time we will close out our series on data sources… So be ready for a good time, next time.


One thought on “Getting rid of data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s