Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The delete statement is used to remove rows from a table. In this lesson I'll be using the scratch database, and I'll be continuing with the test table that we've been using for the last few lessons. So I'll say select star from test. And we can see that those are the rows in the test table. Now, if I want to delete a row from this table, I can use delete from, test, where a equals two, semi-colon.
And this will delete the row where this condition is true, which is just that one row, sense of humor and all, and if I. Select go, we've still got our select statement in there, so we'll see the result. And that row with the two in it is gone. So just as with the update statement, this where clause is used to select which rows are going to be deleted. So, it's a good idea to test the where clause, right? If I say select. Star from test where a equals, and let's put a one in there now.
Then you'll see that that selects just these two rows. And if I go ahead and do a delete, then those two rows'll be deleted and our result will show the null and the three and the four. So if I say here, delete from test where a equals one. Now two rows have been deleted, we can see in our status two rows affected, three rows returned, and we have a null row and all the rows that don't have a 1 or a 2 in A. So it's a good idea to test that where clause with a select if you're typing at a console like we are here, because if we don't included the where clause at all.
All of these rows will be deleted, if I press go now, now all of these rows have been deleted and there's no result left for the select. So we're not going to be using this test table anymore for the rest of the chapter. So I'm going to go ahead and. Delete the actual table which is done with the drop command. We'll see drop later so if I say drop table test that'll actually delete the table from the database and that returns the database to the original state for the rest of the chapter.
So the delete statement is used to delete rows from a table you use the where clause to select the rows to be deleted. Just as you did with the update statement.
Get unlimited access to all courses for just $25/month.Become a member