Until now, we have worked with the creation of databases, tables, data, and retrieval of data. Now let us go over the process of updating data, once the data has been added to the table, there will be different cases where the data has to be updated, such as a typo while adding the student's name, or if the student's address changes after they have registered for the course, and so on. We will use the UPDATE DML statement to modify the data. The UPDATE statement requires a minimum of three details, the first is the name of the table on which this operation will be performed, the second is the name of the column, and the third is the value that the column to has to be assigned to. We can also use the UPDATE statement to modify more than one column at a time. There are two cases where the UPDATE statement can be used. The first case is where all the records in the table will be updated, and this has to be done very carefully as this could cause the loss of existing data. The second scenario when using the UPDATE statement is in combination with the WHERE clause. By using the WHERE clause, we are targeting a very specific set of records based on the filter criteria.
Tip
It is recommended to execute the filter criteria with a SELECT statement, so that we can verify the dataset on which our UPDATE statement would run, in order to make any required changes if the filter criterion does not reflect the expected results. Another way of handling such scenarios is to use a transaction, which will allow us to rollback any changes that we have made.
In the preceding example, we have updated John Dane's current city to Nebraska by using his student ID. We can also verify this by looking at the output on the query console, it returns that the filter criterion was matched for one row, and the update statement was applied for that one row.