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.
2 comments