Commas separate "name-expression" pairs. Generally, it is allowed to use any valid SQL expression. Type a correct combination of literals, operators, functions, and column references here; remember about type consistency—updating an integer column with a text is never a good idea.
ABC Industries Ltd has a lot of data and uses SQL to work with it. Information about their personnel is stored in a table named employees. For each employee, there is a department ID (integer), their last name (text), their salary (integer), and its upper limit (integer):
If, for some reason, all workers need to be moved to department #14, an appropriate query will be:
SET department_id = 14;
The table looks like after running the query will look as below:
New values don’t need constant literals. They are often composed based on data already present in the table cells. Each column reference represents the current value stored in the corresponding row cell.
ABC Industries Ltd decided to give their employees a raise; their current salaries should be used:
SET salary = salary + 10000;
Adding an integer value to an integer column produces a value of integer type, which means that the type consistency requirement is met.
During the execution of UPDATE, every row of a table is considered individually. If we want to use old value(s) to compute a new value for a cell, only cell(s) from the same row will be taken into account.
It’s possible to update multiple columns simultaneously, using only one query instead of two:
If ABC Industries Ltd decides to set new salaries to 80 percent of their upper limits and omit the fractional part that might appear, use the floor() function that takes a real value and returns an integer value.
SET salary = floor(0.8 * upper_limit);
The update is a relatively simple operation but often comes in handy in practice.
Share this article
Get more lessons like this
Thank you! Your submission has been received!
Oops! Something went wrong.
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
A logical expression in the WHERE clause can combine several simple expressions. The only requirement is that an expression must produce a BOOLEAN value for each table row. Only those rows for which the expression produces TRUE will be updated.
A table named groups stores information about the existing student groups in North-Western County College:
Due to the recent policy update from the college administration, the number of students in all groups taking algebra has to be cut to 40. At the same time, Ms. Gibbs realizes that her literature course is more popular than she thought, so she would like to increase the number of students taking her course to 40. To reflect these changes in the table, the query will be:
SET capacity = 40
course LIKE '%Algebra%'
OR tutor = 'Laura Gibbs'
This is how the table looks now:
A WHERE clause doesn't have much in the basic template for UPDATE queries but plays a crucial role. As a first step of composing your query, think about which exact rows need to be updated and write the WHERE part; after that, feel free to change the data any way you want.
General template of an update statement
col1 = expr1,
col2 = expr2,
colN = expr
Using WHERE in the UPDATE operation is easy: it follows the same principle here as it does in other SQL operations.
As data drives decisions in modern industries, understanding how to store, retrieve, and manage it is crucial. Our tracks cover the fundamentals of SQL, teaching you to interact with databases efficiently.