Get startedGet started for free

Delete your data

1. Delete your data

In the last two lessons you've learned how to both create & edit tables in your database. Now you will learn how to delete records and tables in your database.

2. DROP, TRUNCATE, DELETE

The extreme case of removing tables from your database involves dropping them. The DROP TABLE command removes the table you specify from your database. If, you wanted to keep the table structure but remove all of the records in your table the command your would use is TRUNCATE TABLE followed by the name of the table whose records you would like to clear. Finally, if you wanted to remove only selected records from a table you would use the DELETE FROM command followed by the table name from which to delete the records from and use a WHERE statement to specify the conditions that determine which records you would like to delete. Let's work through an example of the last approach.

3. DELETE inactive customers

In this scenario, you need to remove all inactive customers from your database. This means you need to delete all records from the customer table WHERE the active column is equal to FALSE. This translates into the SQL statement shown on the slide.

4. DELETE using a subquery

Just like with UPDATE you can delete records based on conditions identified in other tables by leveraging subqueries. Mirroring the subquery UPDATE example in the last video you can see a very similar approach. Here you start with the DELETE FROM command and limit it to cases where address ids belong to those found in your subquery. In this case the subquery returns the address ids from the address table of customers that reside in the city of Woodridge. The only difference is the DELETE syntax.

5. Let's DELETE some records!

Remember that just like with UPDATE, you need be extra cautious with the DELETE, TRUNCATE & DROP commands when working with a real-world database. With that in mind, it's time to practice what you've learned.

Create Your Free Account

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.