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;

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.

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.

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.

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.

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.

DELETE FROM users;

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.