Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson
1×
Become a member
to unlock all features

Level Up!

Access all courses & lessons on egghead today and lock-in your price for life.

Autoplay

    Removing Data with SQL Delete, Truncate, and Drop

    postgresPostgreSQL

    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.

    Code

    Code

    Become a Member to view code

    You must be a Pro Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    orLog In
    Discuss

    Discuss

    Transcript

    Transcript

    Instructor: 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.

    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.

    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.

    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.

    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.

    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.