UPDATE to Edit Multiple Rows

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:

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:

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:

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:

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

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.