Update data in a table

If you don't already have a database named "library", then make sure to run createdb library from the terminal before starting this chapter. The library database is needed to work through this chapter's content.

In the insert chapter, we created several new records and added it to the users table. In this chapter, we look at how to update and delete existing data in a table.

Updating Data

If you have data in a table that you need to update, it will be a tedious task to update each record manually, or create a new record for each item when one isn't necessary. SQL provides the UPDATE statement as a solution. UPDATE allows the same WHERE clause that we previously saw used in the SELECT query in order to identify which rows to update. It's important to be careful in this respect. A WHERE clause that isn't restrictive or specific enough can affect rows you didn't intend to modify. Or, in the worst case scenario, not providing a WHERE clause to the query will apply the update to all records. As a rule of thumb, always back up your database and test your UPDATE and DELETE queries on dummy data first.

Setup data

Let's setup some dummy data in the users table to help demonstrate the features of the UPDATE statement. The data is provided and located here. You can copy and paste the SQL statements into your prompt. Alternatively, you can run the statements using psql.

psql -d library < update_data.sql

Let's take a look at the insertion commands in the file. The first command deletes all users from the user table and the next 7 insert commands create 7 users in our user table.

DELETE FROM users;

INSERT into users(username, enabled)
VALUES('John Smith', false);
INSERT into users(username, enabled)
VALUES('John Smith II', false);
INSERT into users(username, enabled)
VALUES('John Smith III', false);
INSERT into users(username, enabled)
VALUES('John Smith III', true);
INSERT into users(username, enabled)
VALUES('Jack Smith III', true);
INSERT into users(username, enabled)
VALUES('Jack Smith II', true);
INSERT into users(username, enabled)
VALUES('Jack Smith I', true);

Now your users table should look like the data below, though the ids may vary. The ids are auto generated and depend on how many total records were inserted/deleted from the users table.

library=# SELECT * FROM users;
 id |         username          | enabled
----+---------------------------+---------
  3 | John Smith                | f
  4 | John Smith II             | f
  5 | John Smith III            | f
  6 | John Smith III            | t
  7 | Jack Smith III            | t
  8 | Jack Smith II             | t
  9 | Jack Smith I              | t
(7 rows)

Let's get started

Updating a single row

You might just want to enable John Smith II (the 2nd record) which could be done with the command below:

library=# UPDATE users SET enabled = true
library-# WHERE username = 'John Smith II';
UPDATE 1

In plain english this would translate to:

Change the enabled column to true for all users where the username is John Smith II.

Now take a look at your data and see if the enabled flag has changed for that record.

library=# SELECT * FROM users;
 id |         username          | enabled
----+---------------------------+---------
  3 | John Smith                | f
  5 | John Smith III            | f
  6 | John Smith III            | t
  7 | Jack Smith III            | t
  8 | Jack Smith II             | t
  9 | Jack Smith I              | t
  4 | John Smith II             | t
(7 rows)

Alternatively, you might want to disable all users, which can be done simply by omitting the WHERE clause:

library=# UPDATE users SET enabled = false;
UPDATE 7

In plain english this would translate to:

Change the enabled column to false for all users.

With all users' enabled column set to false, the table data now looks like this:

library=# SELECT * FROM users;
 id |         username          | enabled
----+---------------------------+---------
  3 | John Smith                | f
  5 | John Smith III            | f
  6 | John Smith III            | f
  7 | Jack Smith III            | f
  8 | Jack Smith II             | f
  9 | Jack Smith I              | f
  4 | John Smith II             | f
(7 rows)

Updating multiple rows based on criteria

Since the WHERE part of the action takes the same operators as other queries, you could, for example, enable only users with the roman numerals II in their name. SQL provides the LIKE clause to do such comparisons that require matching to a pattern, in this case II.

library=# UPDATE users SET enabled = true
library-# WHERE rtrim(username) LIKE '% II';
UPDATE 2

This command translated into plain english would read:

Change enabled to true for all users whose name contains II.

rtrim is a function we can use to remove whitespace to the right of a data field. Its use is necessary in the above SQL statement, otherwise we won't get a correct match with our LIKE clause. We'll talk more rtrim and other functions later on. % This is a wild card, telling the SQL LIKE clause to allow any number of characters.

Our table now reads:

library=# SELECT * FROM users;
 id |         username          | enabled
----+---------------------------+---------
  3 | John Smith                | f
  5 | John Smith III            | f
  6 | John Smith III            | f
  7 | Jack Smith III            | f
  9 | Jack Smith I              | f
  8 | Jack Smith II             | t
  4 | John Smith II             | t
(7 rows)

The records for both John Smith II and Jack Smith II are now enabled. We were able to update multiple records with one statement.

Deleting Data

SQL provides the DELETE command to delete data from the database. You can use the WHERE clause to provide criteria and delete the specific records you want from a particular table.

library=# DELETE FROM users WHERE username = 'John Smith';
DELETE 1
library=# SELECT * FROM users;
 id |         username          | enabled
----+---------------------------+---------
  5 | John Smith III            | f
  6 | John Smith III            | f
  7 | Jack Smith III            | f
  9 | Jack Smith I              | f
  8 | Jack Smith II             | t
  4 | John Smith II             | t
(6 rows)

As you can see the user has been deleted, and we have 6 records in the users table.

Summary

Let's quickly recap again:

Command Notes
UPDATE users SET enabled = true WHERE username = 'John Smith II'; Updates the enabled field of the record with username John Smith II in the users table to true
DELETE FROM users WHERE username = 'John Smith' Delete the record with username John Smith from the users table

We know how to update data in a table, but how do we change the table itself? Our users table has only 3 fields. It may not be very useful in a production system. We also need to store the date when the user logged in to the library system; the user's address and an email address to contact them. In the next chapter, we will learn about the ALTER TABLE command and how to change the structure of the table to add/update columns.