UPDATE

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)