Share on Update data in a table 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: We can specify any table in our database to update and specify any number of columns within that table. 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. 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. This includes the row where enabled already had a value of false.
Updating all the rows in a table like this is fairly unusual. 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. This indicates that four of the five rows in our table had the value of enabled set to true. 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". 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. Let's try this out before moving on to look at how to delete all of the rows in a table. Let's delete one of the rows that contain our duplicate name. 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. 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. 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. This would be done in the form: 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. Let's quickly recap the syntax for doing so: 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 characters.
Update all the rows in the table so that this column holds the value Aves. Both should hold strings of up to 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. Add a column to the countries table called continent to hold strings of up to 50 characters. 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.
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. Other accepatable 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. Remove Tom Cruise from the celebrities table.
Change the name of the celebrities table to singers, and remove anyone who isn't a singer. Remove all the rows from the countries table. Change the drink on James Bergman's order from a Cola to a Lemonade. Update the data in the table to reflect this.