1. Updating data in a table
It's also possible to edit update data in a database.
2. Updating data in a table
This is done using the update statement. The update statement works just like an insert, but it has an additional where clause to determine which records will be updated. The values clause contains only the column=value pairs we want to change.
3. Updating one row
In this example, I want to make the employee with the ID of 3 an active employee. I start by importing the update statement from sqlalchemy. Next I build an update statement for the employees table that, uses a where clause to target employee 3 and then in the values clause sets them active. Next I execute the statement and print the rowcount to make sure that only 1 employee was updated.
4. Updating multiple rows
It's possible to update multiple records by having a where clause that would target multiple records. Because this is so easy to do, I always advise you to check the rowcount and make sure the right number of records were updated.
5. Inserting multiple rows
In this example, we need to set all active employees to be inactive and with a salary of 0. We start by building an update statement for the employees table with a where clause that matches all active employees. Then we update the active and salary columns with the changes. Next we execute the statement and finally print the rowcount to make sure I updated the proper number of employees.
6. Correlated updates
We have already restored all the employees back to the prior active status and previous salaries, and now we want to pay all our employees the same amount. We start by building a select statement to select the maximum salary we currently pay any employee. Next we build an update statement for the employees table without a where clause so it will update every record. In the values clause we set the salary column to the select statement. When we execute the statement, it will find the maximum salary in the table using the new_salary select statement and that maximum salary will be used as the value for the salary field in the update statement that affects every record in the table. We can see when we print the rowcount that it affected all three employees.
7. Correlated updates
When you use a select statement to get the value to be used in an update statement as we did in the previous example, this is called a Correlated Update. A correlated update is often used to select data from another table or the maximum value of a column to use as the value of the update.
8. Let's practice!
Now it's your turn to practice updating data.