Modifying Data with UPDATE

How do you modify data already in the database? You could use DELETE to remove a row and then use INSERT to insert a new row, but that is quite inefficient. The UPDATE command allows you to update data already in the database. It follows a format similar to the previous commands.

Continuing with our friend table, suppose Mike had a birthday, so we want to update his age in the table. The example in Figure  shows the word UPDATE, the table name friend, followed by SET, then the column name, the equals sign (=), and the new value.  

        test=> UPDATE friend SET age = 20 WHERE firstname = 'Mike';
        UPDATE 1
        test=> SELECT * FROM friend;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Cindy           | Anderson             | Denver          | CO    |  23
         Sam             | Jackson              | Allentown       | PA    |  22
         Mike            | Nichols              | Tampa           | FL    |  20
        (3 rows)  

The WHERE clause controls which rows are affected by the UPDATE, just as in a DELETE operation.  Without a WHERE clause, all rows are updated.

Notice that the Mike row has moved to the end of the list. The next section will explain how to control the order of the display.