Database Reference
In-Depth Information
Updating Using Values from Other Tables
The SQL UPDATE statement can set a column equal to the value of a column in a different
table. The View Ridge Gallery database has no appropriate example for this operation, so sup-
pose instead that we have a table named TAX_TABLE with columns (Tax, City), where Tax is
the appropriate tax rate for the City.
Now suppose we have a table named PURCHASE_ORDER that includes the columns
TaxRate and City. We can update all rows for purchase orders in the city of Bodega Bay with
the following SQL statement:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH07-05 *** */
UPDATE
PURCHASE_ORDER
SET
TaxRate =
(SELECT
Tax
FROM
TAX_TABLE
WHERE
TAX_TABLE.City = 'Bodega Bay')
WHERE
PURCHASE_ORDER.City = 'Bodega Bay';
More likely, we want to update the value of the tax rate for a purchase order without specifying
the city. Say we want to update the TaxRate for purchase order number 1000. In that case, we
use the slightly more complex SQL statement:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-UPDATE-CH07-06 *** */
UPDATE
PURCHASE_ORDER
SET
TaxRate =
(SELECT
Tax
FROM
TAX_TABLE
WHERE
TAX_TABLE.City = PURCHASE_ORDER.City)
WHERE
PURCHASE_ORDER.Number = 1000;
SQL SELECT statements can be combined with UPDATE statements in many different
ways. We need to move on to other topics, but try these and other variations of UPDATE on
your own.
The SQL MERGE Statement
The
SQL MERGE statement
was introduced in SQL: 2003 and, like the previously dis-
cussed SQL TRUNCATE TABLE statement, is one of the newest additions to SQL. The SQL
MERGE statement essentially combines the SQL INSERT and SQL UPDATE statements
into one statement that can either insert or update data depending upon whether some
condition is met.
For example, suppose that before VRG staff insert data into the ARTIST table, they care-
fully research data about each artist and store it in a table named ARTIST_DATA_RESEARCH.
Data on new artists is initially stored in ARTIST_DATA_RESEARCH, along with correc-
tions to data on artists already in ARTIST. The VRG business rule is that ARTIST names are
never changed after they have been entered, but if errors in Nationality, DateOfBirth or
Search WWH ::
Custom Search