Online College Courses for Credit

+
UPDATE to Edit Row

UPDATE to Edit Row

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose an UPDATE statement that impacts a single row in a table and confirm that the update successfully took place.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the UPDATE statement to update data in a single row in two parts:

  1. UPDATE a single row
  2. RETURNING clause

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>;

So 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 part in the WHERE clause is the condition that 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, and we want to ensure that we are verifying that we add 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 us revisit a scenario that we looked at in a prior tutorial with the referral table. Let us rebuild that table and insert the data in:

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:

File:11363-1440-1.png

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. Although in our case, we only have a single record with Randall Faustino, in a real-world scenario, we may have multiple Randall as the first name and potentially multiple Randall Faustino 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;

File:11364-1440-2.png

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 the same:

SELECT *
FROM customer
WHERE customer_id = 16;

File:11365-1440-3.png

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

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

File:11366-1440-4.png


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:

File:11367-1440-5.png

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:

File:11368-1440-6.png


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.

summary
The UPDATE statement can be used to modify data in a single row.