SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 309

DateDeceased are discovered, these errors will be corrected. In this case, new ARTIST data
can be inserted and ARTIST data updated by using the following SQL MERGE statement:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-MERGE-CH07-01 *** */
MERGE INTO ARTIST AS A USING ARTIST_DATA_RESEARCH AS ADR
ON (A.LastName = ADR.LastName
AND
A.FirstName = ADR.FirstName)
WHEN MATCHED THEN
UPDATE SET
A.Nationality = ADR.Nationality,
A.DateOfBirth = ADR.DateOfBirth,
A.DateDeceased = ADR.DateDeceased
WHEN NOT MATCHED THEN
INSERT (LastName, FirstName, Nationality,
DateOfBirth, DateDeceased);

The SQL DELETE Statement
The SQL DELETE statement is also quite easy to use. The following SQL statement will de-
lete the row for a customer with a CustomerID of 1000:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-DELETE-CH07-01 *** */
DELETE FROM CUSTOMER
WHERE CustomerID = 1000;

Of course, if you omit the WHERE clause, you will delete every customer row, so be care-
ful with this command as well. Note that the DELETE statement without the WHERE clause
is the logical equivalent of the SQL TRUNCATE TABLE statement previously discussed.
However, the two statements use different methods to remove the data from the table and are
not identical. For example, the DELETE statement may fire a trigger (as discussed later in this
chapter), but the TRUNCATE TABLE statement never fires triggers.
The DBMS will enforce all referential integrity constraints when processing DELETE
commands. For example, in the View Ridge Gallery database, you will be unable to delete a
CUSTOMER row if that row has any TRANS children. Further, if a row with no TRANS children
is deleted, any existing CUSTOMER_ARTIST_INT children will be deleted as well. This latter
action occurs because of the CASCADE DELETE specification on the relationship between
CUSTOMER and CUSTOMER_ARTIST_INT.

Using SQL Views

An SQL view is a virtual table that is constructed from other tables or views. A view has no data
of its own, but obtains data from tables or other views. Views are constructed from SQL SELECT
statements using the SQL CREATE VIEW statement , and view names are then used just as
table names would be in the FROM clause of other SQL SELECT statements. The only limitation
on the SQL statements that are used to create views is that they may not contain an ORDER BY
clause. 1 The sort order must be provided by the SELECT statement that processes the view.

1 This limitation appears in the SQL-92 standard, but how views are actually implemented varies by DBMS
product. For example, Oracle Database allows views to include ORDER BY, whereas SQL Server will only allow
ORDER BY in very limited circumstances.