Subtitles of the Movie

We are going to look at Deleting data using SQL and we actually have a couple of tours in our tour table, both of which are named Antarctica. We have one here and we have one here. So, what I want to be able to do is deal with that, or delete that. So how do we delete data? Well, there is no ColdFusion tag to delete data and that is to kind of save you, so to speak. They want to make it a little more difficult to delete data from a database than insert, update and just pull data out. You do need to be careful when you delete data that you specify the WHERE Statement here because that tells the database which row to delete, otherwise you could end up deleting every row if you did not have the WHERE Statement in here. So here's an example and it's simply delete from where. So, delete from the tours table where tour name equals Costa Rica and that would delete the Costa Rica tour row from the tours table. What we're going to do is move into Code view and I actually have a CFQUERY DELETE Statement listed on the page here and all we need to do is copy it out to here. We're going to check this out and delete the Antarctica tour and what this is going to do is we have two rows named Antarctica, let's see what happens. So we want to delete from tours where the tour name equals Antarctica, and that's the only specification we're going to do. Now all I need to do is save this page and run it. When I run it, it should trigger the delete. So this is one, I don't need a form or anything else, this is why you have to be careful with the DELETE Statement. So, I just put it in the browser. If I move down, notice my query was able to run and let's take a look at the database and see what happened with our two rows that said Antarctica and if you look now, what happened is I had two rows for the Antarctica tour and I now have zero. So, if you look into our name I no longer have Antarctica. This is why it's crucial to pass a primary key as your delete value. The primary key in the database is a unique way to identify every individual row, otherwise you can end up doing this type of thing where you're deleting information in multiple rows and you didn't even realize you were. So I used this example purposely. Just have caution when you create your deletes and, generally speaking, you want your WHERE Statement to have a unique identifier for the row so you don't end up deleting too many, in other words more than one, unless you really want to. So that is how to delete data using SQL and here's our DELETE Statement. If I run this again nothing would happen because there's nothing to delete. So that is Deleting Data in ColdFusion.