7 days of WordPress plugins, themes & templates - for free!* Unlimited asset downloads! Start 7-Day Free Trial
FREELessons: 16Length: 1.5 hours

Next lesson playing in 5 seconds

Cancel
  • Overview
  • Transcript

3.4 Delete Query Basics

OK, you've come pretty far with the basic queries. There's really only one more that you need to understand to be able to know how to do just about all the main operations on your table data. This one is the most destructive of all the queries and definitely deserves some attention—the DELETE query.

Related Links

3.4 Delete Query Basics

Finally, we wanna talk a little bit about one of the most destructive commands that there are in SQL, and that is the delete. So update is pretty bad, you can definitely do some damage by updating multiple records that you didn't mean to, and maybe not having tracking, and then really not being able to back your way out of that one. But deleting data is pretty destructive which means once you delete that data, it is gone. So once again, you have to be very particular about what you're going to try to do when you are deleting data. Once again, you wanna be very specific about the data that you are deleting. So let's go ahead and talk a little bit about what this looks like. So just like the other commands, we have a select, we have an update. Now we wanna be able to delete something, and the way that we're gonna do that is by using the delete Query, and in this case, we want to delete data from a table. So we're going to say from, and now we're gonna specify the table name. So I'm gonna say, I want to delete from guest, and what is it that I want to delete? Well, I'm once again going to break out my favorite where clause, and if you didn't kind of put things together when we talked a little bit about the select query, you could add in a where, an and, and you can do ors, and you can chain things together like that. Those things are still applicable for updates and deletes. Just so that you know, we're just keeping things fairly simplistic. For the updates and the deletes, we're only really talking about a single row so we're specifying the unique ID column, but just so you can see how these things work. So now I want to delete from guest, where ID is equal to some value because that's that unique identifier. So let's say Derek is no longer going, and we're not on friendly terms anymore, so I'm not even gonna bother putting him in the database. So I want to delete from guest where id = 1. So let's go ahead and hit Enter, we see that one row is affected. Let's go ahead and do a select, and now you can see that Jane is the only one that is coming to the party so far which is kinda sad. I'm very grateful that Jane's coming to the party, but unfortunately a party with one person isn't much of a party. So in this case, I think we're just gonna have to go ahead and cancel our party. So let's go ahead and finish this up with a final delete statement here. In this case, we're going to do a from guest where ID equals one now. Once again, you don't have to specify this where clause. I could bring it all that way back to delete from guest. And if I were to do that it's going to actually delete everything which is fine in this case because like I said, we had to cancel the party. It really hurts, but sometimes you gotta do what you gotta do. So let's go ahead and do our select again, and you're gonna see that we're left with an empty set which is actually a very good segue. So now we have an empty table. And let's say at this point instead of working with data in the table, I really need to modify the table itself. So right now let's go ahead and say explain guest. And we're gonna see here that we have four columns, ID, first name, last name, and confirmed. Let's say that maybe this isn't quite enough. Maybe we want to track some information about when this record was added to the table, maybe we want to modify a column to say it is. Initially when I created the table, it was marked as a nullable column, so null is yes, but maybe I don't want it to be later on. I wanna be able to make sure that it has a value and things like that, some kinda housekeeping when it comes to your tables, and that's what we're gonna start talking about in the next lesson.

Back to the top