7.4 Updates and Deletes

Up to now you have seen how to create tables, how to insert records, and how to select data. In this section you will learn to modify data and to remove records from a table.

To modify data, SQL provides a command called UPDATE. The next listing shows the syntax overview of the UPDATE command:

 phpbook=# \h UPDATE Command:     UPDATE Description: update rows of a table Syntax: UPDATE [ ONLY ] table SET col = expression [, ...]     [ FROM fromlist ]     [ WHERE condition ] 

UPDATE can be used to modify a list of columns. Just like SELECT statements, UPDATE statements can contain WHERE clauses for restricting the data modified by the UPDATE query.

Take a look at a simple UPDATE operation:

 phpbook=# UPDATE t_person SET city='Mauerbach' WHERE id=2; UPDATE 1 

In every record where the id is equal to 2, city is set to Mauerbach. PostgreSQL displays two parameters returned by the query. The first word defines the operation performed by the query. The second value tells us that one record has been affected by the operation. This value is important because this way it is possible to see what has been caused by the SQL statement.

Let's see if the operation has been performed correctly:

 phpbook=# SELECT * FROM t_person WHERE id=2;  id | name | zip_code |   city ----+------+----------+-----------   2 | Epi  |     1060 | Mauerbach (1 row) 

The value of city is Mauerbach instead of NULL now.

If you want to update more than just one column, it can be done with one UPDATE command as well:

 phpbook=# UPDATE t_person SET name='Ewald Geschwinde', zip_code=2300 WHERE id=2; UPDATE 1 

In this example name and zip_code are modified. Just pass a list of columns, including the new values, to the database and PostgreSQL will change the required values:

 phpbook=# SELECT * FROM t_person WHERE id=2;  id |       name       | zip_code |   city ----+------------------+----------+-----------   2 | Ewald Geschwinde |     2300 | Mauerbach (1 row) 

Both values have been changed.

To remove values from a table, SQL provides the DELETE command. The syntax overview of DELETE is simple:

 phpbook=# \h DELETE Command:     DELETE Description: delete rows of a table Syntax: DELETE FROM [ ONLY ] table [ WHERE condition ] 

Here is an example of a simple DELETE command:

 phpbook=# DELETE FROM t_person WHERE id>3; DELETE 1 

One record has been removed from the table because only one record satisfies the condition passed to the DELETE statement.

If you want to remove all records from a table, you can use TRUNCATE instead of DELETE. Because no condition has to be checked, TRUNCATE can be faster than DELETE. Take a look at the syntax of the command:

 phpbook=# \h TRUNCATE Command:     TRUNCATE Description: empty a table Syntax: TRUNCATE [ TABLE ] name