UPDATE | PostgreSQL (2nd Edition)

Now that you've seen a number of ways to view your data, let's see how to modify (and delete) existing data.

The UPDATE command modifies data in one or more rows. The general form of the UPDATE command is

 UPDATE  table  SET  column = expression  [, ...] [WHERE  condition  ]

Using the UPDATE command is straightforward: The WHERE clause (if present) determines which rows will be updated and the SET clause determines which columns will be updated (and the new values).

You might have noticed in earlier examples that one of the tapes had a duration of '4 days, 01:36' ”that's obviously a mistake. You can correct this problem with the UPDATE command as follows :

 movies=# UPDATE tapes SET duration = '4 hours 36 minutes' movies-#   WHERE tape_id = 'OW-42201'; UPDATE 1 movies=# SELECT * FROM tapes;  tape_id          title          duration ----------+----------------------+----------  AB-12345  The Godfather          AB-67472  The Godfather          MC-68873  Casablanca             OW-41221  Citizen Kane           AH-54706  Rear Window            OW-42200  Sly                   01:36  KJ-03335  American Citizen, An   OW-42201  Stone Cold            04:36 (8 rows)

Using the UPDATE command, you can update all the rows in the table, a single row, or a set of rows ”it all depends on the WHERE clause. The SET clause in this example updates a single column in all the rows that satisfy the WHERE clause. If you want to update multiple columns, list each assignment, separated by commas:

 movies=# UPDATE tapes movies-#   SET duration = '1 hour 52 minutes', title = 'Stone Cold' movies-#   WHERE tape_id = 'OW-42201'; UPDATE 1 movies=# SELECT * FROM tapes;  tape_id          title          duration ----------+----------------------+----------  AB-12345  The Godfather          AB-67472  The Godfather          MC-68873  Casablanca             OW-41221  Citizen Kane           AH-54706  Rear Window            OW-42200  Sly                   01:36  KJ-03335  American Citizen, An   OW-42201  Stone Cold            01:52 (8 rows)

The UPDATE statement displays the number of rows that were modified. The following UPDATE will modify three of the seven rows in the customers table:

 movies=# SELECT * FROM customers;  id     customer_name       phone    birth_date  balance ----+----------------------+----------+------------+---------   1  Jones, Henry          555-1212  1970-10-10     0.00   2  Rubin, William        555-2211  1972-07-10    15.00   3  Panky, Henry          555-1221  1968-01-21     0.00   4  Wonderland, Alice N.  555-1122  1969-03-05     3.00   5  Funkmaster, Freddy    555-FUNK                7  Gull, Jonathon LC     555-1111  1984-02-05    8  Grumby, Jonas         555-2222  1984-02-21  (7 rows) movies=# UPDATE customers movies-#   SET balance = 0 movies-#   WHERE balance IS NULL; UPDATE 3 movies=# SELECT * FROM customers;  id     customer_name       phone    birth_date  balance  ----+----------------------+----------+------------+---------   1  Jones, Henry          555-1212  1970-10-10     0.00   2  Rubin, William        555-2211  1972-07-10    15.00   3  Panky, Henry          555-1221  1968-01-21     0.00   4  Wonderland, Alice N.  555-1122  1969-03-05     3.00   5  Funkmaster, Freddy    555-FUNK                 0.00   7  Gull, Jonathon LC     555-1111  1984-02-05     0.00   8  Grumby, Jonas         555-2222  1984-02-21     0.00 (7 rows)