Basic UPDATE statement

Typically, we don’t just store data: we need it to reflect the current state of things in real life as closely as possible. One can get a promotion at work, so their title needs to be changed. You can buy a gift for your friend and the amount of money in your bank account has to be adjusted accordingly. For cases like these, SQL has a special operation that helps to change values in cells of the already existing rows – UPDATE.

General form

What information is necessary for making an update? Name of a table where we want to change data, column name(s) 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;

"Column name-expression" pairs are separated by commas. Generally, it is allowed to use any valid SQL expression. You can type a correct combination of literals, operators, functions and column references here, just remember about type consistency; trying to update an integer column with a text is never a good idea.

Imagine that you work as a developer for ABC Industries Ltd. The company 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, we could write the following:

UPDATE employees 
SET department_id = 14;

Since we used an integer value in the column department_id of integer type, the query is correct.

Here's what the table looks like after running the query:

Column references

As mentioned earlier, new values don’t have to be constant literals. Oftentimes, they are composed based on data that is already present in the table cells. Each column reference represents the current value stored in the corresponding row cell.

What if we want to celebrate such a massive change in the company's structure and give our employees a raise?
Absolute values won’t do here, so their current salaries should be used:

UPDATE employees 
SET salary = salary + 10000;

The addition of an integer value to an integer column produces a value of integer type as a result, which means that type consistency requirement is met.

It’s possible to update multiple columns simultaneously, so we can achieve the same result using only one query instead of two:

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

Let’s try to come up with something more elaborate: set new salaries to 80 percent of their upper limits and omit the fractional part that might appear. For the last part of the requirement, we can use the floor() function that takes a real value and returns an integer value.

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

As you see, the update is a fairly simple operation, but in practice, it often comes in handy.

Summary

We've seen that updates are really helpful when we need to change some data in a table. All you need is to use the UPDATE ... SET syntax and specify the column name and the new data that you want to insert. It's also possible to update multiple columns and use any SQL functions.

Great! Speaking of practice: are you ready for some tasks?