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) |