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
Updating Records in a Table
The SQL "UPDATE" statement can be used to modify rows that are already in a database table. This lesson demonstrates how to write an UPDATE statement in a Named Query.
Video recorded using: Ignition 8.0
Transcript(open in window)
[00:00] In this lesson, we're going to take a look at how to update data that already exists within the database table. I've queried all the records from my database table here using my select query and I just got some new inventory. I want to update the workstations in my support department, to 23 instead of 22. We can do this using an update query. So to start, I'm going to make a new named query and I'm going to call it update inventory. In the offering tab, I will of course first set my database connection to internal DB and I need to make sure I set my query type to update query since I'm going to be updating data. I don't need any parameters right now, so I'm going to delete both of them and then I will paste my query into the query area.
[01:06] To update data in the database, we start with the command update and then list out the table that we want to update. In this case, the inventory table. We then use the set keyword followed by a column name equal to a value to set the column to a certain value. In this case, I want to update the quantity to be 23. If I ended my query there, this would set the value of the quantity column to 23 for every single row and I don't necessarily want that to happen. I only want to set the quantity to 23 for a specific row. So the last part of my update query needs to be a where clause, where I specify conditions to filter down the row that I want to update until I just have the row that I want selected. In this case, the row that I wanted to update had a name of workstations and a location of support.
[02:06] So I list both of those conditions separated by an and in my where clause. I can go to my testing tab and execute my query to test this out. You can see, we get a result set back of one, indicating that one row was updated. To confirm this worked, I can go back to my select query. My original row had a value of 22 and if I execute this again, I can see that now, it has a value of 23. A few things to note about the update query. First, rather than specifying a hard-coded value of 23 or a name of workstations or a location of support, I can make these dynamic by using parameters. This will allow me to dynamically specify the row that I want to update as well as what the new quantity is. Additionally, I can update multiple columns in a single query by listing out more column names equal of value underneath the set keyword and separating them all with commas.