Database Updates

Database Updates
SQL is a complete data manipulation language that can be used for modifying the data in
the database as well as querying the database. The commands for modifying the database
are not as complex as the SELECT statement. In this section, we describe the three SQL
statements that are available to modify the contents of the tables in the database:
n INSERT – adds new rows of data to a table;
n UPDATE – modifies existing data in a table;
n DELETE – removes rows of data from a table.
Adding data to the database (INSERT)
There are two forms of the INSERT statement. The first allows a single row to be inserted
into a named table and has the following format:
INSERT INTO TableName [(columnList)]
VALUES (dataValueList)
TableName may be either a base table or an updatable view (see Section 6.4), and columnList
represents a list of one or more column names separated by commas. The columnList is
optional; if omitted, SQL assumes a list of all columns in their original CREATE TABLE
order. If specified, then any columns that are omitted from the list must have been declared
as NULL columns when the table was created, unless the DEFAULT option was used
when creating the column (see Section 6.3.2). The dataValueList must match the columnList
as follows:
n the number of items in each list must be the same;
n there must be a direct correspondence in the position of items in the two lists, so that
the first item in dataValueList applies to the first item in columnList, the second item in
dataValueList applies to the second item in columnList, and so on;
n the data type of each item in dataValueList must be compatible with the data type of the
corresponding column.
Example 5.35 INSERT . . . VALUES
Insert a new row into the Staff table supplying data for all columns.
INSERT INTO Staff
VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’, ‘M’, DATE ‘1957-05-25’, 8300,
‘B003’);
As we are inserting data into each column in the order the table was created, there is no
need to specify a column list. Note that character literals such as ‘Alan’ must be enclosed
in single quotes.
Example 5.36 INSERT using defaults
Insert a new row into the Staff table supplying data for all mandatory columns:
staffNo, fName, lName, position, salary, and branchNo.
INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo)
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, 8100, ‘B003’);
As we are inserting data only into certain columns, we must specify the names of the
columns that we are inserting data into. The order for the column names is not significant,
but it is more normal to specify them in the order they appear in the table. We could also
express the INSERT statement as:
INSERT INTO Staff
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, NULL, NULL, 8100, ‘B003’);
In this case, we have explicitly specified that the columns sex and DOB should be set to NULL.
The second form of the INSERT statement allows multiple rows to be copied from one
or more tables to another, and has the following format:
INSERT INTO TableName [(columnList)]
SELECT . . .
TableName and columnList are defined as before when inserting a single row. The SELECT
clause can be any valid SELECT statement. The rows inserted into the named table are
identical to the result table produced by the subselect. The same restrictions that apply to
the first form of the INSERT statement also apply here.
Example 5.37 INSERT . . . SELECT
Assume that there is a table StaffPropCount that contains the names of staff and the number
of properties they manage:
StaffPropCount(staffNo, fName, lName, propCount)
Populate the StaffPropCount table using details from the Staff and PropertyForRent tables.
INSERT INTO StaffPropCount
(SELECT s.staffNo, fName, lName, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.staffNo, fName, lName)
UNION
(SELECT staffNo, fName, lName, 0
FROM Staff s
WHERE NOT EXISTS (SELECT *
FROM PropertyForRent p
WHERE p.staffNo = s.staffNo));
This example is complex because we want to count the number of properties that staff
manage. If we omit the second part of the UNION, then we get only a list of those staff
who currently manage at least one property; in other words, we exclude those staff who
currently do not manage any properties. Therefore, to include the staff who do not manage
any properties, we need to use the UNION statement and include a second SELECT
statement to add in such staff, using a 0 value for the count attribute. The StaffPropCount
table will now be as shown in Table 5.35.
Note that some dialects of SQL may not allow the use of the UNION operator within a
subselect for an INSERT.