Online College Courses for Credit

+
UPDATE to Edit Multiple Rows

UPDATE to Edit Multiple Rows

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose an UPDATE statement that succeeds in bulk updating a result set based on a common characteristic and confirm that the update successfully took place. Then, select the number of records that were updated (this should be provided in output of the update statement).

(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 a set of rows in one statement rather than individually.

The update statements that we have worked on so far have been focused on updating individual rows. The complexity of the UPDATE statements does increase as you do have to be careful of updating rows that you did not intend to update. There are instances where we may want to update the entire table. For example, if we consider that we may have a permanent sale on the tracks to give a 20% discount, we can update the entire table like:

UPDATE track
SET unit_price = unit_price * .8;

Notice with our update statement that we have unit_price = unit_price * .8. This is taking the existing value of unit_price multiplying it by .8 (or discounting it by 20%) and then setting that value to the unit_price. Since we do not have a WHERE clause, this will update every single row.

We will be able to see that the unit_price has been decreased from 0.99 for most tracks to 0.79:

File:11369-1450-1.png

As the WHERE clause is the same between the SELECT and UPDATE statement, it can be beneficial to first use the WHERE clause to verify the rows that you want to update before you add it to the UPDATE statement.

For example, we may want to give a discount on the tracks for a specific album. We can first start by selecting it:

SELECT track_id, genre_id, unit_price
FROM track
WHERE album_id = 1;

This returns us with 10 rows:

File:11370-1450-2.png

Using the same WHERE clause, we can then apply the update:

UPDATE track
SET unit_price = unit_price * .8
WHERE album_id = 1;

If we now query the table, we should see the unit_price being updated:

File:11371-1450-3.png

However, it would not be feasible to check every single other row being that there are thousands of rows in the table. This is where using the RETURNING * would be used to add in the statement. Let us make the same change to those with the album_id set to 3.

UPDATE track
SET unit_price = unit_price * .8
WHERE album_id = 3
RETURNING *;

Immediately after running the statement, we can quickly see that 3 rows were affected by the changed data:

File:11372-1450-4.png

We can also use ranges such as those album_id values between 10-20 to have a 25% discount:

UPDATE track
SET unit_price = unit_price * .75
WHERE album_id BETWEEN 10 AND 20
RETURNING *;

File:11373-1450-5.png

What you will have noticed is that anything you can query and filter using the WHERE clause in the SELECT statement can be filtered in the same manner as the UPDATE statement.


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 update multiple rows at the same time.