SQL UPDATE statement

General form

To update information in general, you need the name of a table, the column name where the data resides, and an expression to calculate a new value for each specified column:

UPDATE table_name 
SET col1 = expr1, 
    col2 = expr2, 
    …, 
    colN = exprN;

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.

Example

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:

UPDATE employees 
SET department_id = 14;

The table looks like after running the query will look as below:

Column references

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:

UPDATE employees 
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:

UPDATE employees 
SET department_id = 14, 
    salary = salary + 10000;

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.

UPDATE employees 
SET salary = floor(0.8 * upper_limit);

The update is a relatively simple operation but often comes in handy in practice.

WHERE clause

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.

Example

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:

UPDATE groups 
SET capacity = 40 
WHERE 
    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

UPDATE table_name 
SET 
    col1 = expr1, 
    col2 = expr2, 
    …, 
    colN = expr 
WHERE 
    logical_expression;

Using WHERE in the UPDATE operation is easy: it follows the same principle here as it does in other SQL operations.

‍