INSERT, DELETE, and UPDATE Statements in SQL

INSERT, DELETE, and UPDATE Statements in SQL

In SQL, three commands can be used to modify the database: INSERT, DELETE, and UPDATE. We discuss each of these in turn.

1. The INSERT Command

In its simplest form, INSERT is used to add a single tuple to a relation. We must spec-ify the relation name and a list of values for the tuple. The values should be listed in the same order in which the corresponding attributes were specified in the CREATE TABLE command. For example, to add a new tuple to the EMPLOYEE relation shown in Figure 3.5 and specified in the CREATE TABLE EMPLOYEE ... command in Figure 4.1, we can use U1:

U1:                               

 INSERT INTO          EMPLOYEE

VALUES                                              ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ‘98

Oak Forest, Katy, TX’, ‘M’, 37000, ‘653298653’, 4 );

A second form of the INSERT statement allows the user to specify explicit attribute names that correspond to the values provided in the INSERT command. This is use-ful if a relation has many attributes but only a few of those attributes are assigned values in the new tuple. However, the values must include all attributes with NOT NULL specification and no default value. Attributes with NULL allowed or DEFAULT values are the ones that can be left out. For example, to enter a tuple for a new EMPLOYEE for whom we know only the Fname, Lname, Dno, and Ssn attributes, we can use U1A:

U1A:                              

INSERT INTO          EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES                                            (‘Richard’, ‘Marini’, 4, ‘653298653’);

Attributes not specified in U1A are set to their DEFAULT or to NULL, and the values are listed in the same order as the attributes are listed in the INSERT command itself. It is also possible to insert into a relation multiple tuples separated by commas in a single INSERT command. The attribute values forming each tuple are enclosed in parentheses.

A DBMS that fully implements SQL should support and enforce all the integrity constraints that can be specified in the DDL. For example, if we issue the command in U2 on the database shown in Figure 3.6, the DBMS should reject the operation because no DEPARTMENT tuple exists in the database with Dnumber = 2. Similarly, U2A would be rejected because no Ssn value is provided and it is the primary key, which cannot be NULL.

U3:    INSERT INTO     EMPLOYEE (Fname, Lname, Ssn, Dno)

          VALUES    (‘Robert’, ‘Hatcher’, ‘980760540’, 2);

          (U2 is rejected if referential integrity checking is provided by DBMS.)

U2A: INSERT INTO     EMPLOYEE (Fname, Lname, Dno)

          VALUES    (‘Robert’, ‘Hatcher’, 5);

          (U2A is rejected if NOT NULL checking is provided by DBMS.)

A variation of the INSERT command inserts multiple tuples into a relation in con-junction with creating the relation and loading it with the result of a query. For example, to create a temporary table that has the employee last name, project name, and hours per week for each employee working on a project, we can write the statements in U3A and U3B:

U3A:                              CREATE TABLE      WORKS_ON_INFO

Emp_name VARCHAR(15), Proj_name VARCHAR(15), Hours_per_week DECIMAL(3,1) );

U3B:       INSERT INTO    WORKS_ON_INFO ( Emp_name, Proj_name,

Hours_per_week )

SELECT                     E.Lname, P.Pname, W.Hours

FROM                        PROJECT P, WORKS_ON W, EMPLOYEE E

WHERE                     P.Pnumber=W.Pno AND W.Essn=E.Ssn;

A table WORKS_ON_INFO is created by U3A and is loaded with the joined informa-tion retrieved from the database by the query in U3B. We can now query WORKS_ON_INFO as we would any other relation; when we do not need it any more, we can remove it by using the DROP TABLE command (see Chapter 5). Notice that the WORKS_ON_INFO table may not be up-to-date; that is, if we update any of the PROJECT, WORKS_ON, or EMPLOYEE relations after issuing U3B, the informa-tion in WORKS_ON_INFO may become outdated. We have to create a view (see Chapter 5) to keep such a table up-to-date.

2. The DELETE Command

The DELETE command removes tuples from a relation. It includes a WHERE clause, similar to that used in an SQL query, to select the tuples to be deleted. Tuples are explicitly deleted from only one table at a time. However, the deletion may propa-gate to tuples in other relations if referential triggered actions are specified in the ref-erential integrity constraints of the DDL (see Section 4.2.2).¹² Depending on the number of tuples selected by the condition in the WHERE clause, zero, one, or sev-eral tuples can be deleted by a single DELETE command. A missing WHERE clause specifies that all tuples in the relation are to be deleted; however, the table remains in the database as an empty table. We must use the DROP TABLE command to remove the table definition (see Chapter 5). The DELETE commands in U4A to U4D, if applied independently to the database in Figure 3.6, will delete zero, one, four, and all tuples, respectively, from the EMPLOYEE relation:

U4A: DELETE FROM  EMPLOYEE

          WHERE     Lname=‘Brown’;

U4B: DELETE FROM  EMPLOYEE

          WHERE     Ssn=‘123456789’;

U4C: DELETE FROM  EMPLOYEE

          WHERE     Dno=5;

U4D: DELETE FROM  EMPLOYEE;

3. The UPDATE Command

The UPDATE command is used to modify attribute values of one or more selected tuples. As in the DELETE command, a WHERE clause in the UPDATE command selects the tuples to be modified from a single relation. However, updating a primary key value may propagate to the foreign key values of tuples in other relations if such a referential triggered action is specified in the referential integrity constraints of the DDL (see Section 4.2.2). An additional SET clause in the UPDATE command specifies the attributes to be modified and their new values. For example, to change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively, we use U5:

U5:    UPDATE    PROJECT

          SET   Plocation = ‘Bellaire’, Dnum = 5

          WHERE     Pnumber=10;

Several tuples can be modified with a single UPDATE command. An example is to give all employees in the ‘Research’ department a 10 percent raise in salary, as shown in U6. In this request, the modified Salary value depends on the original Salary value in each tuple, so two references to the Salary attribute are needed. In the SET clause, the reference to the Salary attribute on the right refers to the old Salary value before modification, and the one on the left refers to the new Salary value after modification:

U6:    UPDATE    EMPLOYEE

          SET   Salary = Salary * 1.1

          WHERE     Dno = 5;

It is also possible to specify NULL or DEFAULT as the new attribute value. Notice that each UPDATE command explicitly refers to a single relation only. To modify multiple relations, we must issue several UPDATE commands.