You can help by commenting or suggesting your edit directly into the transcript. We'll review any changes before posting them. All comments are completely anonymous. For any comments that need a reply, consider emailing firstname.lastname@example.org.
We are experiencing playback issues from our video hosting provider. Please check back shortly.
3:37Creating a Table
3:05Inserting Data into a Table
2:30Selecting Data from a Table
3:25Filtering Select Results
3:34Conditional Where Clause
3:21Updating Records in a Table
2:57Deleting Records from a Table
Take topic challenge
Deleting Records from a Table
The SQL "DELETE" statement will remove one or more records from a single table. This lesson demonstrates how to write a DELETE statement in a Named Query.
Video recorded using: Ignition 8.0
Transcript(open in window)
[00:00] In this lesson, I want to take a look at how we can delete records out of a table in our database. I have all of the rows from my table selected here using my select named query and I want to delete one of these records. Maybe I've gotten rid of the projectors in the support department. So I would like to remove that record from my table entirely. We're going to start by making a new named query and I'm going to call it Delete Records. And we're going to go to the Authoring tab, specify our database connection to internal DB and the query type to update query since we're going to be updating our table. For this example, I don't need parameters, so I'm going to delete both of them out and then I'm going to paste in my query.
[01:04] To delete a row from our table, we first start with the command delete from followed by the name of the table that we want to delete a record from, so in this case inventory. Technically you could end your query there, but it is usually advised to add a where clause to your delete query. The where clause helps narrow down the rows so that you're just deleting the rows you want. In this case, my where clause has a name of projectors and a location of support. As with previous queries, you can substitute parameters in for the values for the name and location columns to dynamically delete rows rather than having hard-coded values. We can test this out by going to the testing tab and hitting execute query. We can see my result set is one, indicating that one record was deleted, which is what I expected.
[02:04] I can go back and check on my table by going back to my select query and executing it again and you'll notice that the support projectors row has been deleted. If your where clause matches multiple rows, then all rows that match that where clause will be deleted. I also want to emphasize the importance of a where clause. If I don't specify a where clause in my delete query, then all rows will match to this delete query and if I were to execute and run this, you can see that now it's deleted seven rows and if I check my table now, you can see that it has deleted all of the records in that table.