SQL 101 – DELETE
It looks like I’m getting close to the end of my 101 series. After I show you how to remove data from tables, the last topic I plan to cover is creating tables. It’s been quite a journey, collecting all this knowledge and putting it out there for you. I had hoped to get some feedback by this point to know if I’m heading in the right direction on these articles. For now, I’ll get these two articles out, then try and figure out what topics remain to be covered.
Now back to our regularly scheduled post.
When you work in SQL, you’ll eventually come across data you want to get rid of. I’m going to side-step the topic of version control, and backups for now. When you get to that day, you’ll need to learn how to use the DELETE command.
DELETE FROM tableName WHERE column = 'value'
Basically you call DELETE FROM then enter the table you want to delete from. The WHERE clause here is as important as it was for the UPDATE statement. If you don’t include the WHERE clause, you’ll end up deleting every row in your table, and you don’t want to do that, do you?
This brings me back to a set up steps nearly identical to those I shared in the UPDATE arcticle.
- SELECT the delete. Before running an delete, run a SELECT statement with your where clause to make sure you’re only getting the records you expect.
- BEGIN a transaction.
- Run the DELETE statement
- SELECT the changes, verify you only deleted the records you think you did.
- COMMIT the transaction.
These six steps will make it far less likely to DELETE something you don’t mean to. There is nothing to prevent you from deleting or updating something you don’t mean to. SQL isn’t like that. There is no prevention, only insurance. Transactions are one form of insurance, having a bulletproof backup and recovery system is the other.
The DELETE command is very simple. You can get fancy with it, and I’ll show you that later. For now, get comfortable with this command. Practice with it on a test server (or your local machine), and soon you’ll be ready for more!
If you have any questions, please send them in! I can only teach you what you want to know if you tell me what that is. I look forward to your comments!