SQL offers various statements for updating the contents (the column values in the rows) of tables—statements for inserting new rows, for changing column values, and for deleting rows. This chapter describes the extensive features of these statements.
Note
In most examples of this book, we assume that the tables contain their original contents. If you execute the statements discussed in this chapter, you change the contents of the tables. Consequently, the results of your statements in the next examples can differ from those in the book. On the Web site of the book, www.r20.nl, you can read how to restore the original contents of the tables after an update.
In SQL, you can use the INSERT statement to add rows to an existing table. With this statement, you can add new rows and populate a table with rows taken from another table.
<insert statement> ::= INSERT INTO <table specification> <insert |
specification>
<insert specification> ::=
[ <column list> ] <values clause> |
[ <column list> ] <table expression>
<column list> ::=
( <column name> [ { , <column name> }... ] )
<values clause> ::=
VALUES <row expression> [ { , <row expression>
Section 4.8, in Chapter 4, “SQL in a Nutshell,” among others, contains several examples of INSERT statements. In this section, we show other simple examples to illustrate the possibilities of the INSERT statement.
Example 14.1. The tennis club has a new team. This third team will be captained by player 100 and will compete in the third division.
INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION) VALUES (3, 100, 'third')
Explanation: Behind the term INSERT INTO, the name of the table is specified for which rows must be added. Following that are the names of the columns of that table; finally, a VALUES clause is used to specify the values of the new row. The structure of a VALUES clause is simple and consists of one or more row expressions, with each row expression consisting of one or more scalar expressions.
The word INTO can be left out, but with all other SQL products, it is required; therefore, we recommend including the word always.
You do not have to specify column names if a value is specified for all columns of the table concerned. The TEAMS table contains three columns, and three values have been specified, which means that we could have omitted the column names:
INSERT INTO TEAMS VALUES (3, 100, 'third')
If column names are omitted, SQL assumes that the order in which the values are entered is the same as the default sequence of the columns (see COLUMN_NO in the COLUMNS table).
You are not required to specify columns in the default sequence. Therefore, the next statement is equivalent to the previous two:
INSERT INTO TEAMS (PLAYERNO, DIVISION, TEAMNO) VALUES (100, 'third', 3)
If the column names had not been specified in this statement, the result would have been entirely different. SQL would have considered the value 100 to be a TEAMNO, the value third a PLAYERNO, and the value 3 a DIVISION. Of course, the insert would not have been performed at all because the value third is an alphanumeric literal and the PLAYERNO column has a numeric data type.
For all columns in the CREATE TABLE statement that have been defined as NOT NULL, a value must be specified (work out for yourself why). The following statement is, therefore, not correct because the PLAYERNO column has been defined as NOT NULL and does not have a value in the INSERT statement:
INSERT INTO TEAMS
(TEAMNO, DIVISION)
VALUES (3, 'third')
However, the next example is correct.
Example 14.2. Add a new player.
INSERT INTO PLAYERS
(PLAYERNO, NAME, INITIALS, SEX,
JOINED, STREET, TOWN)
VALUES (611, 'Jones', 'GG', 'M', 1977, 'Green Way',
'Stratford')
In all columns that have not been specified in the INSERT statement, NULL values will be entered.
Instead of a literal, a NULL value can be specified. Then, the concerning row will be filled, in that row, with the NULL value. In the following statement, the LEAGUENO column, among other things, will be filled with NULL:
INSERT INTO PLAYERS
(PLAYERNO, NAME, INITIALS, BIRTH_DATE,
SEX, JOINED, STREET, HOUSENO, POSTCODE,
TOWN, PHONENO, LEAGUENO)
VALUES (611, 'Jones', 'GG', NULL, 'M', 1977,
'Green Way', NULL, NULL, 'Stratford', NULL, NULL)
Because it is possible to specify more than one row expression in a VALUES clause, you can add multiple new rows with one INSERT statement.
Example 14.3. Add four new teams.
INSERT INTO TEAMS (TEAMNO, PLAYERNO, DIVISION)
VALUES (6, 100, 'third'),
(7, 27, 'fourth'),
(8, 39, 'fourth'),
(9, 112, 'sixth')
Explanation: The new rows are separated by apostrophes within the VALUES clause.
Instead of literals, you can also include expressions within the VALUES clause, and these expressions can be compound. Therefore, calculations, scalar functions, and even scalar subqueries are allowed.
Example 14.4. Create a new table in which the number of players and the sum of all penalties will be stored.
CREATE TABLE TOTALS
(NUMBERPLAYERS INTEGER NOT NULL,
SUMPENALTIES DECIMAL(9,2) NOT NULL)
INSERT INTO TOTALS (NUMBERPLAYERS, SUMPENALTIES)
VALUES ((SELECT COUNT(*) FROM PLAYERS),
(SELECT SUM(AMOUNT) FROM PENALTIES))
Explanation: Remember that each subquery must be placed between brackets in this construct.
In the previous section, we showed only examples of INSERT statements in which new rows are added. With the INSERT statement, we can fill a table with rows from another table (or other tables). You could say that data is copied from one table to another. Instead of the VALUES clause, we use a table expression in the INSERT statement.
Example 14.5. Create a separate table in which the number, name, town, and telephone number of each noncompetition player is recorded.
We start with creating a new table:
CREATE TABLE RECR_PLAYERS
(PLAYERNO SMALLINT NOT NULL,
NAME CHAR(15) NOT NULL,
TOWN CHAR(10) NOT NULL,
PHONENO CHAR(13),
PRIMARY KEY (PLAYERNO))
The following INSERT statement populates the RECR_PLAYERS table with data about recreational players registered in the PLAYERS table:
INSERT INTO RECR_PLAYERS
(PLAYERNO, NAME, TOWN, PHONENO)
SELECT PLAYERNO, NAME, TOWN, PHONENO
FROM PLAYERS
WHERE LEAGUENO IS NULL
After this INSERT statement, the contents of the new table look like this:
PLAYERNO NAME TOWN PHONENO
-------- ------- --------- ----------
7 Wise Stratford 070-347689
28 Collins Midhurst 071-659599
39 Bishop Stratford 070-393435
95 Miller Douglas 070-867564
Explanation. The first part of the INSERT statement is a normal INSERT statement. The second part is based not on a VALUES clause, but on a table expression. The result of a table expression is a number of rows with values. However, these rows are not displayed on the screen, but are stored directly in the RECR_PLAYERS table.
The rules that apply to the first form of the INSERT statement also apply here. The next two statements, then, have an equivalent result to the previous INSERT statement:
INSERT INTO RECR_PLAYERS
SELECT PLAYERNO, NAME, TOWN, PHONENO
FROM PLAYERS
WHERE LEAGUENO IS NULL
INSERT INTO RECR_PLAYERS
(TOWN, PHONENO, NAME, PLAYERNO)
SELECT TOWN, PHONENO, NAME, PLAYERNO
FROM PLAYERS
WHERE LEAGUENO IS NULL
Several other rules apply:
The table to which rows are added can be the same as the one from which they are copied.
The table expression is a fully fledged table expression and, therefore, can include subqueries, joins, set operators,
GROUP BYandORDER BYclauses, functions, and so on.The number of columns in the
INSERT INTOclause must be equal to the number of expressions in theSELECTclause of the table expression.The data types of the columns in the
INSERT INTOclause must conform to the data types of the expressions in theSELECTclause.
We use two examples to illustrate the first rule.
Example 14.6. Duplicate the number of rows in the RECR_PLAYERS table.
INSERT INTO RECR_PLAYERS
(PLAYERNO, NAME, TOWN, PHONENO)
SELECT PLAYERNO + 1000, NAME, TOWN, PHONENO
FROM RECR_PLAYERS
Explanation. One thousand is added to the value of the PLAYERNO column to make sure that no problems occur with the primary key.
Example 14.7. Add all penalties to the PENALTIES table for which the amount is greater than the average amount.
INSERT INTO PENALTIES
SELECT PAYMENTNO + 100, PLAYERNO, PAYMENT_DATE, AMOUNT
FROM PENALTIES
WHERE AMOUNT >
(SELECT AVG(AMOUNT)
FROM PENALTIES)
With the UPDATE statement, you can change values in a table. Use a table reference to indicate which table needs to be updated. The WHERE clause of an UPDATE statement specifies which rows must be changed; the SET clause assigns new values to one or more columns.
<update statement> ::=
UPDATE <table reference>
SET <column assignment> [ { , <column
|
Example 14.8. Update the league number for player 95 to 2000.
UPDATE PLAYERS SET LEAGUENO = '2000' WHERE PLAYERNO = 95
Explanation: For every row in the PLAYERS table (UPDATE PLAYERS) in which the player number equals 95 (WHERE PLAYERNO = 95), you must change the LEAGUENO to 2000 (SET LEAGUENO = '2000'). The last specification is called a column assignment.
In most examples, it is not necessary, but you can specify a pseudonym behind a table name, just as in a SELECT statement. The earlier UPDATE statement has the same result as the following:
UPDATE PLAYERS AS P SET P.LEAGUENO = '2000' WHERE P.PLAYERNO = 95
A literal is specified in the column assignment of this first example. Because of this, the LEAGUENO column gets a complete new value, one that replaces the existing value completely. A column assignment can also contain complex expressions that can even refer to the column that will be updated.
Explanation: Because the WHERE clause has been omitted, as in the previous example, the update is performed on all rows in the table concerned. In this example, the amount in each row of the PENALTIES table is increased by 5%.
Example 14.10. Set the number of sets won to 0 for all competitors resident in Stratford.
UPDATE MATCHES
SET WON = 0
WHERE PLAYERNO IN
(SELECT PLAYERNO
FROM PLAYERS
WHERE TOWN = 'Stratford')
The earlier examples show SET clauses with only one column assignment. You can update multiple columns with one statement simultaneously.
Example 14.11. The Parmenter family has moved to 83 Palmer Street in Inglewood; the postcode has become 1234UU, and the telephone number is unknown.
UPDATE PLAYERS
SET STREET = 'Palmer Street',
HOUSENO = '83',
TOWN = 'Inglewood',
POSTCODE = '1234UU',
PHONENO = NULL
WHERE NAME = 'Parmenter'
Explanation: In this case, the PHONENO column has been filled with the NULL value. Remember the comma between each item. With this statement, both players named Parmenter are moved to the same address.
We must be careful when the column that will be updated is used in the expressions of column assignments. The following statement could give the impression that the values of the STREET and TOWN columns for player 44 are exchanged:
UPDATE PLAYERS
SET STREET = TOWN,
TOWN = STREET
WHERE PLAYERNO = 44
Explanation. The original contents of the PLAYERS table are:
PLAYERNO STREET TOWN -------- ------------ --------- 44 Lewis Street Inglewood
The result of the UPDATE statement is:
PLAYERNO STREET TOWN -------- ------------ --------- 44 Inglewood Lewis Street
So, the values of the columns have not been processed, but now the question is, why not? This is caused by the processing method of the UPDATE statement. For each row, SQL checks whether the condition in the WHERE clause is true. If so, the value of the expression of the first column assignment is determined first, and this value is assigned to the column concerned. The value of the second expression is determined next, and that value also is assigned to the column concerned. In this example, it means that first the value of the TOWN column is assigned to the STREET column. After that, the value of the STREET column in the second column assignment is calculated, which already is the TOWN column. It looks as if SQL has processed the following statements in succession:
UPDATE PLAYERS SET STREET = TOWN WHERE PLAYERNO = 44 UPDATE PLAYERS SET TOWN = STREET WHERE PLAYERNO = 44
When exchanging values, the values of one of the columns must be entered in a temporary table.
Portability
Not all SQL products would process the previous example as described. For example, products such as DB2 would switch the values of the two columns TOWN and STREET.
Expressions consisting of scalar subqueries can also be used in the SET clause.
Example 14.12. Create a new table to store for each player the player number, the number of matches she played, and the sum of all penalties incurred by her.
CREATE TABLE PLAYERS_DATA
(PLAYERNO INTEGER NOT NULL PRIMARY KEY,
NUMBER_MAT INTEGER,
SUM_PENALTIES DECIMAL(7,2))
INSERT INTO PLAYERS_DATA (PLAYERNO)
SELECT PLAYERNO FROM PLAYERS
UPDATE PLAYERS_DATA AS PD
SET NUMBER_MAT = (SELECT COUNT(*)
FROM MATCHES AS M
WHERE M.PLAYERNO = PD.PLAYERNO),
SUM_PENALTIES = (SELECT SUM(AMOUNT)
FROM PENALTIES AS PEN
WHERE PEN.PLAYERNO = PD.PLAYERNO)
Explanation: In the UPDATE clause of the UPDATE statement, a pseudonym (PD) is used for reference in the subqueries to this table.
In a subquery that is used in a SET clause, you can specify the table that will be updated.
Example 14.13. Subtract the average penalty amount from each penalty amount.
UPDATE PENALTIES
SET AMOUNT = AMOUNT – (SELECT AVG(AMOUNT)
FROM PENALTIES)
Explanation: SQL calculates the value of the scalar subquery before the actual UPDATE statement is processed.
The DELETE statement removes rows from a table. The definition of the DELETE statement reads:
<delete statement> ::= DELETE FROM <table reference> [ <where clause> ] <table reference> ::= <table specification> [ [ AS ] <pseudonym> ] <pseudonym> ::= <name> |
Example 14.14. Delete all penalties incurred by player 44.
DELETE FROM PENALTIES WHERE PLAYERNO = 44
or
DELETE FROM PENALTIES AS PEN WHERE PEN.PLAYERNO = 44
If the WHERE clause is omitted, all the rows of the specified table are deleted. This is not the same as dropping a table with the DROP statement. DELETE removes only the contents, whereas the DROP statement deletes not only the contents of the table, but also the definition of the table from the catalog. After the DELETE statement, the table remains intact.
Example 14.15. Delete all players for whom the year in which they joined the club is greater than the average year that all players from Stratford joined the club.
DELETE
FROM PLAYERS
WHERE JOINED >
(SELECT AVG(JOINED)
FROM PLAYERS
WHERE TOWN = 'Stratford')
Explanation: Just as with the UPDATE statement, some SQL products do not allow subqueries in the WHERE clause of a DELETE statement to refer to the table from which rows are deleted. Again, this restriction does not apply to SQL.