The SQL UPDATE Statement

How to change or amend a value in our table.

If one value in our data changes, the last thing we want is to have to remove records and add the whole lot back into a new row. So the UPDATE statement is a really useful tool to have at our disposal.

Let’s say our business has had a really good year and at annual pay review time we decide to give all of our employees a very pleasant 10% payrise.

As we’re updating all employees’ salary we can write an UPDATE statement like this:

UPDATE employees SET salary = salary * 1.1;

What we’re doing here is telling the SQL code that we want to modify or update the table called “employees“, specifically the field in that table called “salary“.

What we want to do with the “salary” field is, on an employee by employee basis, take the existing “salary” amount for that employee and multiply it by 1.1 (i.e. adding an additional 10% to the salary amount).

The SQL code works out what the new salary is and updates the record accordingly for every employee in our table.

Hurrah, drinks are on our crew!

This works really well if everyone in the table is getting that same level of payrise but what if only a certain grade of employee is getting a rise? How do we filter the employees and only modify the chosen employees’ salaries and not the rest?

For this we will go back to a handy clause we learnt in a previous article in this series- the WHERE clause.

Just as we saw that we could effectively filter records into (or out of) the data we brought back from the database with the SELECT statement by using WHERE, we can do much the same when choosing which rows of data get the UPDATE treatment.

For example, if we only wanted to give the Managers in our business a 10% payrise we would code the SQL like this:

UPDATE employees
SET salary = salary * 1.1
WHERE grade='Manager';

By adding the WHERE clause we have limited the UPDATE to only those employees who have a ‘Manager’ grade in our database table. So now the managers are celebrating their good fortune but the poor plebs on the factory floor are stuck where they were on the same pay.

Welcome to capitalism.

The WHERE clause is always a useful one to keep an eye on. There isn’t a SQL developer in the world who hasn’t got carried away and updated every row in their database by accident when they should have used the WHERE clause to filter their targets.

You should have plenty of experience with WHERE by now after all of the previous lessons but it's always worth a refresher if you aren't sure. Better safe than (very) sorry.