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


(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.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.