SQL UPDATE Statement
The UPDATE statement allows you to update existing rows with new values.
With it, you can change the value of one or more columns in each row that meets the search condition of the WHERE clause.
The UPDATE statement is made up of three parts:
- The table you want to update
- The column names with their new values
- The condition that determines which row to update
UPDATE table_name SET column1 = value, column2 = value, .. WHERE condition;
To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.
This table is part of an ‘Employee Management System’ that contains basic information about employees.
UPDATE Single Column
The simplest use of the UPDATE statement is to update a single column in a table.
Let’s take a simple example. Suppose the fourth employee (ID = 4) moved from ‘New York’ to ‘Newark’, therefore that row needs updating. The following query will perform this update:
UPDATE Employees SET City = 'Newark' WHERE ID = 4;
The UPDATE statement always starts with the name of the table to be updated. In our case, it’s the ‘Employees’ table. The SET clause is then used to assign the new value to the specified column. As the SET clause here, sets the ‘City’ column to the new value ‘Newark’. The UPDATE statement ends with a WHERE clause that tells the DBMS which row to update. Here it’s the fourth row.
Special care must be taken when using UPDATE, because if you accidentally omit the WHERE clause you will end up updating every row in the table.
UPDATE Employees SET City = 'Newark';
UPDATE Multiple Columns
You can also update multiple columns at once. When updating multiple columns, only a single SET keyword is used, and each column=value pair is separated by a comma.
In this example, columns ‘City’, ‘Age’ and ‘Salary’ will be updated for the fourth employee.
UPDATE Employees SET City = 'Newark', Age = 26, Salary = 58000 WHERE ID = 4;
UPDATE Multiple Rows
With the UPDATE statement, you can update multiple rows at once. To update several rows, use a WHERE clause that selects only the rows you want to update.
For example, you might want to increase the salary of everyone over the age of 25 by 10%.
UPDATE Employees SET Salary = Salary * 1.1 WHERE Age > 25;
Note that the expression Salary * 1.1 in this example returns the salary increased by 10%.
UPDATE When Corresponding Rows Exist
Sometimes you want to update rows in one table when corresponding rows exist in another. For example, if an employee appears in table ‘Employee_Bonus’, you want to increase that employee’s salary (in table Employees) by 10%.
Suppose the ‘Employee_Bonus’ table looks like this:
There are three ways to achieve this.
Method 1: Using IN operator
You can use a subquery in the WHERE clause of your UPDATE statement to find employees in the ‘Employees’ table that are also in the ‘Employee_Bonus’ table. Your UPDATE will then work only on those rows, so that you will be able to increase their salary by 10 percent.
UPDATE Employees SET Salary = Salary * 1.1 WHERE ID IN (SELECT ID FROM Employee_Bonus);
The results from the subquery represent the rows that will be updated in table ‘Employees’. The IN operator tests the IDs from the ‘Employees’ table to see if they are in the list of IDs returned by the subquery. When they are, the corresponding ‘Salary’ values are updated.
Method 2: Using EXISTS clause
Alternatively, you can use EXISTS instead of IN:
UPDATE Employees SET Salary = Salary * 1.1 WHERE EXISTS (SELECT NULL FROM Employee_Bonus WHERE Employees.ID = Employee_Bonus.ID );
Method 3: Using FROM clause
Above example can also be solved using the FROM clause of the UPDATE statement. The FROM clause contains the names of tables that are involved in the UPDATE statement. All these tables must be subsequently joined.
UPDATE Employees SET Salary = Salary * 1.1 FROM Employees, Employee_Bonus WHERE Employees.ID = Employee_Bonus.ID;
UPDATE with Values from Another Table
You wish to update rows in one table using values from another. For example, you have a table called ‘New_Salary’, which holds the new salaries for certain employees. The contents of the table ‘New_Salary’ are:
Now you want to update the salaries of certain employees in the ‘Employees’ table using values from the ‘New_Salary’ table, if there is a match. It is quite common for updates such as this one to be performed via Inner Join.
UPDATE Employees SET Employees.Salary = New_Salary.Salary FROM Employees INNER JOIN New_Salary ON Employees.ID = New_Salary.ID;
Conditional UPDATE with CASE
Sometimes you need to update the column conditionally, at that time you can use a CASE expression.
For example, you want to increase each employee’s salary by (20, 10, or 5) percent based on their previous salary. Those who earn less than $ 20000 will get a 20% increase, those who have a salary of more than $ 20000 but less than $ 50000 will get a 10% increase and others will get a 5% increase.
UPDATE Employees SET Salary = CASE WHEN Salary > 0 AND Salary < 20000 THEN Salary * 1.2 WHEN Salary >= 20000 AND Salary < 50000 THEN Salary * 1.1 ELSE Salary * 1.05 END
Python Example for Beginners
Two Machine Learning Fields
There are two sides to machine learning:
- Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
- Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.
Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes
Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!
Latest end-to-end Learn by Coding Recipes in Project-Based Learning:
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.