UPDATE statement in SQL

UPDATE, is the DML command that allows you to update database records. In other words, UPDATE allows you to modify the values of the table columns respecting the corresponding data type.

The syntax is as it follows:

UPDATE tableName SET columName = newValue WHERE conditions

As with the DELETE statement, it is very important to take into account the WHERE clause to prevent changes in the table from being propagated to all the records.

To better understand how it works, we are going to perform some examples of UPDATE on the Customers and Employees tables.

Reviewing the records of the Customers table, we notice that some of them were inserted with spelling errors that need to be corrected:

SELECT *
FROM Customers
WHERE customerId = 1;

The last name Haynes was written with an i instead of a y:

UPDATE Customers
SET lastName = 'Haynes'
WHERE customerId = 1;

We run the query again and check that the record has been successfully updated:

Now we will see another example:

SELECT *
FROM Customers
WHERE customerId = 2;

The last name Pettitt is missing a t, and the street name should start with a capital letter.

UPDATE Customers
SET lastName = 'Pettitt',
    [address] = '2336 Cottonwood Lane'
WHERE [name] = 'Mavin';

We run the query again and check that the record has been successfully updated:

Let’s move on to the following example:

SELECT *
FROM Orders
WHERE orderId = 1;

The Xiaomi product is missing the O and we also want to change the orderDate to 2022-07-10:

UPDATE Orders
SET product = 'Xiaomi Mi 11',
    orderDate = '2022-07-10'
WHERE orderId = 1;

To delete the value of a column, just do an UPDATE and set the field to NULL or '' depending on its constraints.

UPDATE Orders
SET productDescription = NULL
WHERE orderId = 2;

It is also possible to use UPDATE in tables joined by some JOIN clause:

UPDATE C SET bankAccount= 111111111
FROM Customers C
INNER JOIN Orders O
ON C.customerId = O.customerId
WHERE O.price > 45
AND C.customerId = 4;

In this case, since there is a product in the Orders table with a price greater than 45 euros for the client with customerId = 4, the customers’s account has been correctly updated to the new value.

También es posible recuperar el valor de una SELECT para actualizar un campo:

UPDATE Orders
SET productDescription = (SELECT productDescription
						  FROM Orders
						  WHERE orderId = 1)
WHERE orderId = 2;