DML: Update

There are any number of reasons why you might need to update the values stored in a record in your database. Whether you are actually changing a value or filling in a column that was previously NULL, the format of the UPDATE statement is always the same. It works in a very similar way to an ordinary query in that you need to locate the row or rows you want to change using a WHERE clause. So for example, if we want to show that someone has borrowed the new copy of Connolly and Begg from our library we could use the following statement:

    UPDATE copy
    SET    status = 'On loan'
    WHERE  copy_id = 1200;

As you might expect, there are some variations on this basic form of the UPDATE statement. If, for example, you want to update more than one column in the target records, you can do this in a single statement by specifying all of the value changes in the SET clause. Using this method, we could change the authors' names on a book to include the full first name rather than just the initial:

    UPDATE book
    SET    main_author = 'Connolly, Thomas',
           other_authors = 'Begg, Caroline'
    WHERE  isbn = '978-0321210258';

Because you need to be certain that your update statement is correct before actually changing any data, it is often a good idea to write a query first which uses the same WHERE clause. When your results show only the records that you want to update, exchange the SELECT clause for the UPDATE and SET clauses.

Another variation is to use a subquery to select the new value from the database. Say for example that a librarian had mistakenly entered the isbn for "Database Systems" by Coronel instead of that for Connolly and Begg when creating a new COPY record. This could be corrected using the following statement:

    UPDATE copy
    SET    isbn = (SELECT isbn
                   FROM   book
                   WHERE  title LIKE 'Database Systems%'
                   AND    main_author LIKE 'CONNOLLY%')
    WHERE  copy_id = 1200;

DML: Update

Further reading