In previous chapters we've created several new rows in our
users table. When adding rows to 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:
last_loginevery time user logs into the application
In this chapter, we look at how to do complete such tasks using
DELETE statements. If you think back to our CRUD acronym,
DELETE statements are analogous to the Update and Delete CRUD operations.
UPDATE statement can be written with the following syntax:
UPDATE table_name SET [column_name1 = value1, ...] 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.
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
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.
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.
sql_book=# UPDATE users SET enabled = false; UPDATE 5
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
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 lets us update only the specific rows that meet the conditon(s) set in that clause. We previously set all users to be disabled. Lets re-enable a few of those accounts. We want to make Harry Potter and Jane Smith active users once again.
sql_book=# UPDATE users SET enabled = true sql_book-# 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
As long as
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 targetting a specific row. Let's do that in order to change one of our duplicate Jane Smiths to have the name "Alice Walker".
sql_book=# 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.
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.
DELETE statement is used to remove entire rows from a database table. The form of a delete statement is somewhat similar to
DELETE FROM table_name WHERE (expression);
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.
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.
sql_book=# DELETE FROM users sql_book-# WHERE full_name='Harry Potter' AND id > 3; DELETE 1
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
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
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;
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 unkown 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:
WHEREclause, with our
SETclause we can use
NULLsince it's not being used as comparison operator in this situation.
NOT NULLconstraint, then it's not possible to set its value to
NULL. An error will be thrown.
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
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
DELETE statements it's sensible to be a bit cautious. It's typical to first do a
SELECT to verify which rows you are targetting. You can then issue the
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
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.
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:
|UPDATE table_name SET [column_name1 = value1, ...] WHERE (expression);||Update specified fields within a table. The rows updated are dependent on the
|DELETE table_name WHERE (expression);||Delete rows in the specified table. Which rows are deleted is dependent on the
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.
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
encyclopedia=# ALTER TABLE animals encyclopedia-# ADD COLUMN class varchar(100); ALTER TABLE encyclopedia=# UPDATE animals SET class = 'Aves'; UPDATE 5
Add two more columns to the
animals table called
kingdom. Both should hold strings of up to 100 characters.
Update all the rows in the table so that
phylum holds the value
Animalia for all the rows in the table.
encyclopedia=# ALTER TABLE animals encyclopedia-# ADD COLUMN phylum varchar(100), encyclopedia-# ADD COLUMN kingdom varchar(100); ALTER TABLE encyclopedia=# UPDATE animals encyclopedia-# SET phylum = 'Chordata', kingdom = 'Animalia'; UPDATE 5
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.
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
encyclopedia=# ALTER TABLE countries encyclopedia-# ADD COLUMN continent varchar(50); ALTER TABLE encyclopedia=# UPDATE countries encyclopedia-# SET continent = 'North America' encyclopedia-# WHERE name = 'USA'; UPDATE 1 encyclopedia=# UPDATE countries encyclopedia-# SET continent = 'Asia' encyclopedia-# WHERE name = 'Japan'; UPDATE 1 encyclopedia=# UPDATE countries encyclopedia-# SET continent = 'Europe' encyclopedia-# WHERE name = 'France' OR name = 'Germany'; UPDATE 2
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
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
encyclopedia=# UPDATE celebrities encyclopedia-# SET deceased = true encyclopedia-# WHERE first_name = 'Elvis'; UPDATE 1 encyclopedia=# ALTER TABLE celebrities encyclopedia-# ALTER COLUMN deceased encyclopedia-# SET NOT NULL; ALTER TABLE
Note that although in PostgreSQL boolean values display as
f in the results of a
f are not valid literal boolean values unless used in single quote marks:
'f'. Other accepatable literals are
false without quote marks; or
'1' with quote marks for
'0' with quote marks for
Remove Tom Cruise from the
encyclopedia=# DELETE FROM celebrities encyclopedia-# WHERE first_name = 'Tom' encyclopedia-# AND last_name = 'Cruise'; DELETE 1
You could also have used
id to specifiy the row.
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.
encyclopedia=# ALTER TABLE celebrities encyclopedia-# RENAME TO singers; ALTER TABLE encyclopedia=# SELECT * FROM singers encyclopedia-# WHERE occupation encyclopedia-# NOT LIKE '%Singer%'; id | first_name | occupation | date_of_birth | deceased | last_name ----+------------+------------+---------------+----------+----------- 2 | Scarlett | Actress | 1984-11-22 | f | Johansson (1 row) encyclopedia=# DELETE FROM singers encyclopedia-# WHERE occupation encyclopedia-# NOT LIKE '%Singer%'; DELETE 1
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 targetting the correct rows.
Remove all the rows from the
encyclopedia=# DELETE FROM countries; DELETE 4
Connect to the
ls_burger database. Change the drink on James Bergman's order from a Cola to a Lemonade.
ls_burger=# UPDATE orders ls_burger-# SET drink = 'Lemonade' ls_burger-# WHERE id = 1; UPDATE 1
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.
ls_burger=# UPDATE orders ls_burger-# SET side = 'Fries', ls_burger-# side_cost= 0.99, ls_burger-# customer_loyalty_points = 13 ls_burger-# WHERE id = 4; UPDATE 1
The cost of Fries has increased to $1.20. Update the data in the table to reflect this.
ls_burger=# UPDATE orders ls_burger-# SET side_cost = 1.20 ls_burger-# WHERE side = 'Fries'; UPDATE 3