Searched Update Statement
The syntax for the searched UPDATE statement is given below.
update_stmt:
UPDATE [database_name.]table_name [[AS] correlation_name]
SET column_name = value_expr [, column_name = value_expr]...
[WHERE {conditional_expr | CURRENT OF cursor_name}]
The values to which the named columns in the SET clause are assigned are the evaluated results of the specified column expressions. The column values in [db_name.]table_name referenced by the expressions are the pre-updated column values. The rows that are updated are those for which conditional_expr is true. If the update of any of the selected rows results in an referential integrity violation (i.e., a foreign key column in the table is changed to a value that does not exist in the referenced table), the update is aborted and the changes to the rows that had already been modified are discarded. If the WHERE clause is not specified, all of the rows in the specified table are updated.
Use of a sub_query in an UPDATE statement is described in the Subqueries chapter.
If one of the columns specified in the SET clause is a primary key that is referenced by one or more foreign key references in other tables then one of two results can occur. If the foreign key declaration in the CREATE TABLE statement of the referencing table is specified with ON UPDATE CASCADE then the update will succeed and the column values of all referencing rows will automatically (and instantly) be updated accordingly. If no on clause is specified or if ON UPDATE RESTRICT is specified, the update will be rejected with a referential integrity error.
The following query lists the unsold books priced at £25,000 and above in the order in which the books were acquired.
select bookid, date_acqd, price, title from book where date_sold is null and price >= 25000.00 order by date_acqd; BOOKID DATE_ACQD PRICE TITLE shakespeare01 2006-01-02 175000.00 The Tragicall Historie of Hamlet, Prince... poe02 2006-02-14 25000.00 Tales of the grotesque and arabesque decartes01 2006-03-09 75000.00 Principia philosophiae twain01 2006-08-06 32500.00 The celebrated jumping frog of Calaveras ... shakespeare07 2006-10-26 25000.00 Works. 1709 shakespeare03 2007-05-22 75000.00 Macbeth, a tragedy. shakespeare06 2007-08-22 34500.00 King Richard II twain03 2007-09-17 67500.00 The adventures of Tom Sawyer, potter04 2007-12-19 80000.00 The tale of Peter Rabbit shakespeare04 2008-02-09 250000.00 Plays wells02 2009-03-24 30000.00 The island of Doctor Moreau, woolf03 2009-08-10 32500.00 Jacob's room [by] Virginia Woolf. shelley01 2009-11-26 25000.00 Frankenstein; or, The modern Prometheus. raleigh01 2010-01-12 32500.00 The history of the world.
Given the difficult economic conditions and because they have been sitting in inventory unsold for some time, the shop owner has decided to lower the price by 15% on the most expensive books that were acquired prior to 2007. The following UPDATE statement will do this.
Note that the values in the date_acqd and date_sold columns in your installation of the bookshop database example will be comprised of dates later than those shown here.
update book set price = price - price*0.15
where date_sold is null and date_acqd < date "2007-01-01" and price >= 25000.00;
**** 5 rows affected
select bookid, date_acqd, price, title from book
where date_sold is null and price >= 25000.00 order by date_acqd;
BOOKID DATE_ACQD PRICE TITLE
shakespeare01 2006-01-02 148750.00 The Tragicall Historie of Hamlet, Prince...
decartes01 2006-03-09 63750.00 Principia philosophiae
twain01 2006-08-06 27625.00 The celebrated jumping frog of Calaveras ...
shakespeare03 2007-05-22 75000.00 Macbeth, a tragedy.
shakespeare06 2007-08-22 34500.00 King Richard II
twain03 2007-09-17 67500.00 The adventures of Tom Sawyer,
potter04 2007-12-19 80000.00 The tale of Peter Rabbit
shakespeare04 2008-02-09 250000.00 Plays
wells02 2009-03-24 30000.00 The island of Doctor Moreau,
woolf03 2009-08-10 32500.00 Jacob's room [by] Virginia Woolf.
shelley01 2009-11-26 25000.00 Frankenstein; or, The modern Prometheus.
raleigh01 2010-01-12 32500.00 The history of the world.
It was also noticed that the bookid values in the book table all begin with the author's last name followed by a two-digit ordered sequence. However, two authors share the same last name: Emily and Charlotte Bronte. The bookid values for the two sisters begin with the first initial to differentiate between the authors. The shop owner was to change this so that the initial follows the last name in order to preserve the last name bookid convention. Since all foreign key references to bookid have been declared with the ON UPDATE CASCADE specification, it is possible to update the bookid column even though it is the book table's primary key. The following example shows the UPDATE statements that do this. Notice the use of the built-in string function replace.
select bookid, last_name, title from book where last_name like "Bronte%"; BOOKID LAST_NAME TITLE cbronte01 BronteC Jane Eyre. An autobiography. Ed. by Currer Bell [pseud.] cbronte02 BronteC Villette. cbronte03 BronteC Jane Eyre. ebronte01 BronteE Wuthering Heights. A novel. update book set bookid = replace(bookid, "cbronte", "brontec") where last_name = "BronteC"; *** 3 rows affected update book set bookid = replace(bookid, "ebronte", "brontee") where last_name = "BronteE"; *** 1 rows affected select bookid, last_name, title from book where last_name like "Bronte%"; BOOKID LAST_NAME TITLE brontec01 BronteC Jane Eyre. An autobiography. Ed. by Currer Bell [pseud.] brontec02 BronteC Villette. brontec03 BronteC Jane Eyre. brontee01 BronteE Wuthering Heights. A novel.
One final comment. Notice that in none of the above examples was a COMMIT statement issued. Hence, the changes made by the foregoing update statements have not yet been permanently stored in the database. Since, these were just examples, let's just go ahead and issue a ROLLBACK statement to discard them.
rollback;
select bookid, date_acqd, price, title from book
where date_sold is null and price >= 25000.00 order by date_acqd;
BOOKID DATE_ACQD1 PRICE TITLE
shakespeare01 2006-01-02 175000 The Tragicall Historie of Hamlet, Prince...
poe02 2006-02-14 25000 Tales of the grotesque and arabesque
decartes01 2006-03-09 75000 Principia philosophiae
twain01 2006-08-06 32500 The celebrated jumping frog of Calaveras ...
shakespeare07 2006-10-26 25000 Works. 1709
shakespeare03 2007-05-22 75000 Macbeth, a tragedy.
shakespeare06 2007-08-22 34500 King Richard II
twain03 2007-09-17 67500 The adventures of Tom Sawyer,
potter04 2007-12-19 80000 The tale of Peter Rabbit
shakespeare04 2008-02-09 250000 Plays
wells02 2009-03-24 30000 The island of Doctor Moreau,
woolf03 2009-08-10 32500 Jacob's room [by] Virginia Woolf.
shelley01 2009-11-26 25000 Frankenstein; or, The modern Prometheus.
raleigh01 2010-01-12 32500 The history of the world.
select bookid, last_name, title from book where last_name like "Bronte%";
BOOKID LAST_NAME TITLE
cbronte01 BronteC Jane Eyre. An autobiography. Ed. by Currer Bell [pseud.]
cbronte02 BronteC Villette.
cbronte03 BronteC Jane Eyre.
ebronte01 BronteE Wuthering Heights. A novel.