UPDATE to Edit Row

1. UPDATE a Single Row

The UPDATE statement is used to modify data that is already in a table. The syntax looks like the following:

UPDATE <tablename>
SET <column1> = <value1>, <column2> = <value2>, ….
WHERE <condition>;

First, we need to specify the table name that we will be updating. Then in the SET clause, we need to specify what the columns are and what values they will be set to. Any columns in the table that are not specified in the SET clause will just keep the values that they currently have.

The condition in the WHERE clause determines which rows to update. If we do not set a WHERE clause, the UPDATE statement will run on all rows in the table. In most cases, this is not what we want to do, so we want to ensure that we are adding the WHERE clause in the UPDATE statement. Luckily, the structure of the WHERE clause in the UPDATE statement should be very familiar to you, as it is the same structure as you have used in the SELECT statement and will use in the DELETE statement.

Let's revisit a scenario that we looked at in a prior tutorial. Let's rebuild that referral table and insert the data:

CREATE TABLE referral( referral_id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50) );

INSERT INTO referral (first_name,last_name,email)
VALUES ('Sandra','Boynton','s.boy@email.com'),
('Randall','Faustino','s.boy@email.com'),
('Park','Deanna','p.deanna@email.com'),
('Sunil','Carrie','s.carrie@email.com'),
('Jon','Brianna','j.brianna@email.com'),
('Lana','Jakoba','l.jakoba@email.com'),
('Tiffany','Walker','t.walk@email.com');

As a reminder, the issue was that Randall Faustino has the same email as Sandra Boynton, when Randall’s email should be r.faustino@email.com instead:

We could update this by doing the following:

UPDATE referral
SET email = 'r.faustino@email.com'
WHERE first_name = 'Randall';

However, to ensure that we are updating only Randall Faustino’s record, it is ideal to update it using the primary key. While in our case, we only have a single record with Randall Faustino, in a real-world scenario, we may have multiple Randalls as the first name and potentially multiple Randall Faustinos as well. If we update it using the primary key, we can ensure that we are only updating that single row:

UPDATE referral
SET email = 'r.faustino@email.com'
WHERE referral_id = 2;

We can also update multiple columns at the same time. For example, if we have a customer (Frank Harris) that has moved to Orlando, we may need to update his address, city, state, and postal_code if he kept the rest of his information the same:

SELECT * 
FROM customer
WHERE customer_id = 16;

UPDATE customer
SET address = '555 International Parkway', city = 'Orlando',state = 'FL', postal_code = '33133-1111'
WHERE customer_id = 16;

Notice the UPDATE statement and the SELECT statement’s similarities with the WHERE clause. In making the update, we should see Frank Harris’s information change:

2. RETURNING Clause

Similar to the INSERT statement, the UPDATE statement also has a RETURNING clause in PostgreSQL. This returns the updated rows:

UPDATE referral
SET email = 'r.faustino@email.com'
WHERE referral_id = 2
RETURNING *;

This allows us to quickly validate the affected rows:

For example, if we accidentally forgot the WHERE clause:

UPDATE referral
SET email = 'r.faustino@email.com'
RETURNING *;

We would be able to see that all of the rows were affected and now had the incorrect email address:

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.