Delete & Truncate

Video Transcription

Welcome back everyone. In this video, we’re going to be starting our discussion into the Delete clause and our data modification series. So remember, our SQL language has two primary parts, the data manipulation language, which is what we’ve been working with primarily so far, and the data definition language, which involves things like the CREATE TABLE statement, we primarily worked with the SELECT clause for most of this course. But the SELECT clause is one of six statements that we may see in the data manipulation language, or DML. So what we’ve covered with so far with Dave, the DML, as far as data modification goes has been insert so far. And in this video, we’re going to be taking a look at the Delete functionality. And then later, we’ll start looking at how we might update data. And then also combine the Update, Delete and so on and together with into one clause using the MERGE statement. But first, let’s take a look at delete. So primarily in SQL, there are two ways you can delete data. The first one being the Delete clause, this is the most common way to delete information, it does support in T SQL, it does support the from clause as well. So delete from but that is non standard as far as the SQL language is concerned. So you may see different structures or different syntax and different database systems. So a T SQL or Microsoft SQL Server versus things like Postgres, or MySQL, the syntax may vary slightly. But delete is an atomic operation. Meaning that if your Delete clause is going to delete, let’s say five rows from Table A, then either all five rows get deleted, or none of them do. So the the delete operation cannot be interrupted. So it’s either all or nothing. As far as that is concerned. Later in this course, we’ll talk about transactions, which can help help support that animosity as far as the database statements go, but the other way you can delete data from a table and SQL is the truncate command, truncate is more destructive, of course, truncate is going to delete all rows from whatever you’re working with. And this is actually very minimally logged. So truncate is actually, if you were to remove all tables from a table, truncate would actually perform faster than the then the Delete clause, but truncate is, is quite destructive, right? You want to be careful when you use this command, because it does delete everything, it does delete all the rows.

And at least as far as the table is concerned, it’s also if you have an identity column for dealing with T SQL here, it does reset the identity value back to where it starts. So that can be very beneficial. The Delete clause does not do that. So if you happen to use the Delete clause to remove all rows from a table does not reset the identity value back to the starting point. So that is one benefit of the truncate command. If that is what you’re going for. Now, you do have to be careful using truncate if there are foreign keys being used. So if there are any foreign keys that are referencing this particular table, truncate will not work. So that is also a limitation of the truncate command. But that prevents us from deleting on unintended rows from other tables that are referencing that. But journal syntax here that looks for for these commands, we have the a standard delete and then non standard delete and then our non standard truncate. So non standard here is going to allow us to use things like a an alias as part of our deletion. Whereas the standard delete does not. That’s the primary difference between the two. And then we also have a truncate command as well. And truncate is not a fancy standard it is specific to T SQL. Although you may see you may see similar types of operations in other database systems. But let’s take a look at some examples on how we might actually execute our SQL here. So for this video, there is a corresponding setup script that’s going to add all of our demo tables back into our database. So that person address, and so on, along with some starting values here.

So make sure you run that script before you start executing any of these queries here. And we can also take a look at what this actually looks like. So if we pull out all of that data as part of a just select star command, you can see all of our records here. So here is our person table and our person address table. So let’s first start off with a simple delete. So I’m going to start out by I want to delete, let’s say, Joe from our table. So delete from person address where Person ID equals two and address type id equals two. And then if we and this is not deleting the person itself, but deleting the person from address, so this would be person address ID here. So delete where the address type is two, and the person ID is two, just in case, if this person had more than one address being listed there, we can delete only one, that one specific one. So let’s give this a run. Alright, so you notice now, for Person ID two, I have deleted a one of their addresses, I have deleted their address that was of type two. And if we select star from demo, dot address, type. And execute that, you can see that I deleted that person’s work address, but not their home address. So I left their home address intact, but removed their work address. Now, of course, you can delete more than one row at a time. So we can say Delete. And remember the from here is optional, we don’t have to add, we don’t have to say delete from here, we can just say delete, and then the name of the table. So delete demo dot person address where personality equals one. And so if we look here, we have these two rows here. So Person ID one person ID one, they have a home address and work address. And so if we run this statement here, ah, now we are two rows shorts now. So we can see that the both of those were deleted as a result. We can also if we, if we like we can use sub queries as part of our deletes. So here, I am selecting a, you’re using a sub query here as part of the where clause for my delete, so delete person address where a person ID is three. So that’s this row here, where and where address type ID is work. So this is something like as I don’t know, what the identify the identifiers are for the each of the individual address types. Using a sub query here and using the more human readable version can can make our query a little bit more readable. And so there we delete that person’s work address. We can also delete all the rows in the table as well. But let’s first I’m running out of rows here to delete.

So let’s go ahead and run that setup script again real quick. So we get all of our rows back. And then let’s just outright delete demo person address. And so if we run this, we can see it deleted all rows from our table. And so of course we can check to see if there’s anything left and of course there isn’t because we deleted all the rows because we had no WHERE clause and so it just deleted everything. And of course we can also So use a, a delete, let me go ahead and run the startup query again, and then do the similar thing. So let’s say delete demo person where not exists. And then we select everything from the person address table. And the person ID, in that person address does not equal or equals the person ID from the person table. So if we run this zero rows affected, basically, this is just a delete deleting people who had no address. And so since I have all the addresses, then I don’t delete any, any people because everyone has an address. But if I go in, and let’s go ahead and delete all of the people again, there we go. And then when I, if I run this, I actually deleted four rows from my person table, because those are the people who had no addresses. So if we do this, select star from demo dot person and every Korea so now we have no people left because we deleted all of the records in the address table. And then we also deleted all the people who had no addresses. We also deleted the people who had no addresses. So that is some examples of sub queries. And what happens when you delete everything. T SQL also supports the non standard form, allowing the full FROM clause.

So what I’ve shown so far, with things like delete from demo dot person to address that is standard in the from clause, the from word there is not required. But we can actually let’s go ahead and also run the setup script again real quick, we can now actually have the full FROM clause. And so that means we can do aliases, we can do derive tables, all sorts of things as part of this. So now inside of my where not exists, I actually can say P dot Person ID, from my outside table, right from my outside table. So this is a very, again, a little bit more expressive, a little bit more user friendly, as far as being able to use aliases and things like that in T SQL, but again, this syntax here is not required. So meaning that you know, if we have our full from syntax, we can also use joints, and the standard ancy standard delete clause, we can actually utilize joints. But in T SQL, we are able to do that because we can use the full FROM clause. So here, delete pa from demo dot person address as PA, enter join on address type, where Person ID equals three and T dot name equals work. So before, right, I had to use a sub query. So let me bring that statement back up here. So these two clauses here are identical. So this is what we had before. This is anti standard. This is anti standard. This down here is SQL Server specific. And some other database systems may allow you to do this, but it is not part of the the the ancy standard. But as you can see here, it’s a little bit cleaner to read when compared to using a sub query. So the join allows us to do a little bit more, a little bit more of an expressive delete clause versus the anti standard which requires a sub query is to do the exact same thing. So if we run that I may delete this back here, and let’s execute that. There we go. And so you can see that I deleted one row and then pull the rest of the room. So I deleted Person ID three Where their address was work. So and they only had a work address, so I deleted it from completely from the table. So that is going to be mostly just mostly our delete clause.

So we’ve covered both of these syntax here, the standard deletion, and the non standard deletion. Remember, the primary difference here between standard and non standard is that in the standard, we can’t use the full from clauses just from table. But and the non standard delete supports the full, the full FROM clause. So we can use table sources, we can do joins, all of those sorts of things can be included here in the from clause, and the standard is just from table and then in non standard we can do from and then anything that goes into your standard FROM clause, as if it was a SELECT clause. Then we also are going to talk about our truncate here, as well. So truncate, just like our so truncate, just like where we deleted all rows, we can very quickly say truncate and truncate is going to remove everything from the demo address table. And so what is also nice about this is that if we see if we look at the identity column, the current ID ID is going to be one, because we have completely refreshed our table. So I can show you what happens here. And a little bit more detail. If we reset our tables, and then execute this again, you can see before the truncate our identity column is six after the truncate is one. This is again faster than saying this is faster than saying just this delete here. And as well. I load this back up. And this, you can see that my identity column, when I do delete results in the same identity, right, the identity doesn’t get reset. So six would be the next value that would be inserted for that identity column as a result of inserting a new record, but truncate here resets it back to wherever, whatever it started to. And you’re not going to really see the execution time here is is you know, I have very, very little data to deal with. But you can see that the total execution time is not going to be really it’s not going to match. This one is technically faster at the moment, because there’s just not enough rows here. But truncate in general for larger sets of data are going to be are going to be faster statements to just completely remove everything inside of our inside of our table. But that will conclude all of our statements that we have listed here to remove data from our database