Back to Open Book Shelf

Update data in a table

In previous chapters we've created several new rows in our users table. When adding rows to a database table, it's unlikely that the data in those rows will remain the same forever. There are many situations where you may need to update and delete the rows that already exist. With our users table for example, we might want to:

  1. Change the value of someone's full_name
  2. Fix a typo
  3. Update the enabled column for a specific user
  4. Delete an incorrect entry
  5. Update the value of last_login every time a user logs into the application

In this chapter, we look at how to accomplish such tasks using UPDATE and DELETE statements. If you think back to our CRUD acronym, UPDATE and DELETE statements are analogous to the Update and Delete CRUD operations.

Updating Data

An UPDATE statement can be written with the following syntax:

UPDATE table_name
SET column_name = value, ...
WHERE expression;

The ellipsis (...) indicates that you can specify multiple column_name = value entries.

This statement can be read as, "Set column(s) to these values in a table when an expression evaluates to true". We can specify any table in our database to update and specify any number of columns within that table.

The WHERE clause in the above syntax example is optional. If omitted, PostgreSQL will update every row in the target table, so before executing such a query be sure that this is actually what you want to do. Even when using a WHERE clause care must be taken to ensure that it is restrictive or specific enough to target only the rows that you want to modify. You can always test your WHERE clause in a SELECT statement to check which rows are being targeted, before then using it in an UPDATE statement.

Let's try out UPDATE with a few simple examples. First we'll look at updating all of the rows in a table, and then how to target specific rows to be updated.

Update All Rows

One thing we might want to do in our users table is disable all of our users at once, for example in response to a security issue.

UPDATE users SET enabled = false;

UPDATE 5

The UPDATE 5 response tells us how many rows had the value for the enabled column set to false by our UPDATE statement. This includes the row where enabled already had a value of false.

Update All Rows

Updating all the rows in a table like this is fairly unusual. In general, you'll update specific rows based on some criteria by including a WHERE clause.

Update Specific Rows

Using a WHERE clause lets us update only the specific rows that meet the condition(s) set in that clause. We previously set all users to be disabled. Let's re-enable a few of those accounts. We want to make Harry Potter and Jane Smith active users once again.

UPDATE users SET enabled = true
             WHERE full_name = 'Harry Potter'
                OR full_name = 'Jane Smith';

UPDATE 4

Notice that on this occasion the response is UPDATE 4. This indicates that four of the five rows in our table had the value of enabled set to true. These are the four rows that matched the conditions in our WHERE clause.

Update Specific Rows

As long as the WHERE clause is specific enough, we can even update a single user as well. Since all of our rows have unique values in the id column, that's a good column to use in our WHERE clause when targeting a specific row. Let's do that in order to change one of our duplicate Jane Smiths to have the name "Alice Walker".

UPDATE users SET full_name='Alice Walker' WHERE id=2;

UPDATE 1

Here, only a single row matched the conditions specified in our WHERE clause, and so only that row was updated.

Update a single specific row

Deleting Data

Sometimes simply updating the data in a row isn't enough to fix a particular data discrepancy, and you need to remove that row altogether. This is where the DELETE statement comes in.

The DELETE statement is used to remove entire rows from a database table. The form of a delete statement is somewhat similar to UPDATE:

DELETE FROM table_name WHERE expression;

As with UPDATE, the WHERE clause in a DELETE statement is used to target specific rows. Let's try this out before moving on to look at how to delete all of the rows in a table.

Delete Specific Rows

We resolved our duplicate 'Jane Smith' issue by updating the full_name of one of those rows, but we still have two Harry Potters. Let's delete one of the rows that contain our duplicate name.

DELETE FROM users
WHERE full_name='Harry Potter' AND id > 3;

DELETE 1

The DELETE 1 response tells us how many rows were deleted by our DELETE statement. In this case only one row matched the conditions in our WHERE clause and so that row was deleted.

Delete a single row

We used both the full_name and the id columns to target the Harry Potter record for deletion. We could have used just the id column to achieve the same result; adding the full_name column to our condition adds some extra protection from inadvertently typing in the wrong value for id.

Delete all Rows

It's rare that you will want to delete all the rows in a table. If you did want to do this however, it can be done with a very simple statement. Just as with UPDATE, the WHERE clause in a DELETE statement is optional. If omitted, all the rows in the table will be deleted.

We'll want to use the current data in the users table in the next section. Take note of the following SQL statement, and how you can delete all rows from a table if you need to, but don't run it against your current database.

DELETE FROM users;

Delete a single row

Update vs Delete

One key difference to keep in mind between how UPDATE works and how DELETE works: with UPDATE you can update one or more columns within one or more rows by using the SET clause; with DELETE you can only delete one or more entire rows, and not particular pieces of data from within those rows.

Although it's not possible to delete specific values within a row, we can approximate this by using NULL. You may remember in an earlier chapter we explained that NULL is a special value which actually represents an absent or unknown value. By using an UPDATE statement to SET a specific value to NULL, although not deleting it as such, we are effectively removing that value.

This would be done in the form:

UPDATE table_name SET column_name1 = NULL
WHERE expression;

A couple of things to note here:

  • Unlike with a WHERE clause, with our SET clause we can use = with NULL since it's not being used as comparison operator in this situation.
  • If a column has a NOT NULL constraint, then it's not possible to set its value to NULL. An error will be thrown.

Use Caution

Although we've demonstrated how to update or delete all the rows in a table, the vast majority of the time this is probably not what you want to do. If you are about to run an UPDATE or DELETE statement that doesn't have a WHERE clause, be certain that you want to actually affect the entire table.

Even if you are using a WHERE clause in your UPDATE or DELETE statements it's sensible to be a bit cautious. It's typical to first do a SELECT to verify which rows you are targeting. You can then issue the UPDATE or DELETE with the same modifiers, being confident that you will only affect the rows that you intend to. It's rare to just issue an UPDATE or DELETE command without verifying first, and probably not a good idea. This is especially the case with DELETE since you will remove the entire row from the table.

Summary

In this chapter we covered the 'U' and 'D' of CRUD, which is Updating and Deleting data. Let's quickly recap the syntax for doing so:

Statement Notes
UPDATE table_name SET column_name = value, ... WHERE expression; Update specified fields within a table. The rows updated are dependent on the WHERE clause. We may update all rows by leaving out the WHERE clause. The ellipsis (`...`) indicates that we can specify as many `column_name = value` pairs as needed.
DELETE FROM table_name WHERE expression; Delete rows in the specified table. Which rows are deleted is dependent on the WHERE clause. We may delete all rows by leaving out the WHERE clause.

We've now covered the various statements needed to create and work with single tables in a database. For an extremely simple application a single table may be all you need. The vast majority of the time you will need several different tables related to each other in various ways in order to model the data structures that your application needs.

In the next part of this book we'll start working with more than one table and explore the various types of relationship that can exist between them.

Exercises

  1. Make sure you are connected to the encyclopedia database. Add a column to the animals table called class to hold strings of up to 100 characters.

    Update all the rows in the table so that this column holds the value Aves.

    Solution

    ALTER TABLE animals
ADD COLUMN class varchar(100);

UPDATE animals SET class = 'Aves';

  2. Add two more columns to the animals table called phylum and kingdom. Both should hold strings of up to 100 characters.

    Update all the rows in the table so that phylum holds the value Chordata and kingdom holds Animalia for all the rows in the table.

    Solution

    ALTER TABLE animals
ADD COLUMN phylum varchar(100),
ADD COLUMN kingdom varchar(100);

UPDATE animals
SET phylum = 'Chordata', kingdom = 'Animalia';

    You could write the UPDATE statement as two separate statements, but you can actually SET multiple columns by comma separating the column name and values pairs.

  3. Add a column to the countries table called continent to hold strings of up to 50 characters.

    Update all the rows in the table so France and Germany have a value of Europe for this column, Japan has a value of Asia and the USA has a value of North America.

    Solution

    ALTER TABLE countries
ADD COLUMN continent varchar(50);

UPDATE countries
SET continent = 'North America'
WHERE name = 'USA';

UPDATE countries
SET continent = 'Asia'
WHERE name = 'Japan';

UPDATE countries
SET continent = 'Europe'
WHERE name = 'France' OR name = 'Germany';

    Here we need three separate UPDATE statements since there are three different values that we want to update for the same column. For the France and Germany rows, since they both have the same value for continent, we can update these within a single statement by using the OR operator in our WHERE clause.

  4. In the celebrities table, update the Elvis row so that the value in the deceased column is true. Then change the column so that it no longer allows NULL values.

    Solution

    UPDATE celebrities
SET deceased = true
WHERE first_name = 'Elvis';

ALTER TABLE celebrities
ALTER COLUMN deceased
SET NOT NULL;

    Note that although in PostgreSQL boolean values display as t or f in the results of a SELECT query, t and f are not valid literal boolean values unless used in single quote marks: 't', 'f'. Other acceptable literals are true or false without quote marks; or 't', 'true', 'y', 'yes', 'on', '1' with quote marks for true, and 'f', 'false', 'n', 'no', 'off', '0' with quote marks for false.

  5. Remove Tom Cruise from the celebrities table.

    Solution

    DELETE FROM celebrities
WHERE first_name = 'Tom'
AND last_name = 'Cruise';

    You could also have used id to specify the row.

  6. Change the name of the celebrities table to singers, and remove anyone who isn't a singer.

    There is a NOT LIKE operator which works in the same way as the LIKE operator except it matches rows where the search string is not present in the overall string.

    Solution

    ALTER TABLE celebrities
RENAME TO singers;

SELECT * FROM singers
WHERE occupation
NOT LIKE '%Singer%';

     id | first_name | occupation | date_of_birth | deceased | last_name
----+------------+------------+---------------+----------+-----------
  2 | Scarlett   | Actress    | 1984-11-22    | f        | Johansson
(1 row)

    DELETE FROM singers
WHERE occupation
NOT LIKE '%Singer%';

    It is prudent before deleting data, especially with a more complex condition, to run a SELECT query first to ensure that the WHERE clause is targeting the correct rows.

  7. Remove all the rows from the countries table.

    Solution

    DELETE FROM countries;

  8. Connect to the ls_burger database. Change the drink on James Bergman's order from a Cola to a Lemonade.

    Solution

    UPDATE orders
SET drink = 'Lemonade'
WHERE id = 1;

  9. Add Fries to Aaron Muller's order. Make sure to add the cost ($0.99) to the appropriate field and add 3 loyalty points to the current total.

    Solution

    UPDATE orders
SET side = 'Fries',
side_cost= 0.99,
customer_loyalty_points = 13
WHERE id = 4;

  10. The cost of Fries has increased to $1.20. Update the data in the table to reflect this.

    Solution

    UPDATE orders
SET side_cost = 1.20
WHERE side = 'Fries';
Previous:  More on Select
Next:  Create Multiple Tables