SQL Fundamentals
Removing Data with SQL Delete, Truncate, and Drop
Deleting data within our table is easily done by using ether the delete, truncate, or just dropping the table altogether from our database. Let’s remove a user from our table using the delete command, remove everyone with truncate, then drop the entire table with the drop command.
Instructor: [00:00] Our users table currently has two rows of data, one for Danny Clark and a Debbie Jones. When we need to actually remove rows from our tables, instead of just updating column data, we'll use the DELETE command. If we don't put a WHERE clause with our statement here, it's going to loop over each row over our table and delete everything.
[00:21] Make sure, before running the DELETE command on your table, you have a conditional clause that targets only the rows you want to delete. After defining which table we're removing rows from, we're saying, delete all the rows where the last name column has a value of the text Clark.
[00:37] We're also able to add other combinations of conditions here within the WHERE clause. For example, we could say, where the last name is Clark and the first name is Danny, or even use the OR statement here, as well.
[00:49] The key point here is, you want to make sure you're targeting the correct rows of data when deleting so that you don't actually lose the wrong rows. If you wanted to delete everything in our table, I mentioned you could just use the DELETE command without a condition.
[01:02] However, using TRUNCATE is a more pro forma way to do so. Unlike the DELETE command, it doesn't scan over the entire table. We also have the ability to truncate more than one table with just one statement by listing the other tables with commas.
[01:18] Finally, if we're trying to remove the table completely from our database, we use the DROP command. Once we run this command, all of the data is deleted and we cannot query anything from this table.