Changing Data

There are three primary statements that you use to change data in existing tables. These are INSERT, UPDATE, and DELETE. Unlike the SELECT statement demonstrated in the preceding section, these statements do not return a result set.

INSERT, UPDATE, and DELETE are discussed in the following sections.

Inserting Records

You insert data into a table using the INSERT SQL statement. At a minimum, you must specify which table data is being inserted into, as well as the values to insert into fields of the table, in order of the table’s structure. For example, consider the following SQL script:

CREATE TABLE TEMP1 (   "Full Name" CHAR(30),   "Number of Visits" SHORT,   "Credit Limit" MONEY,   Active LOGICAL); INSERT INTO TEMP1 VALUES ('Bob Smith', 1, 1000, True);

The first statement creates a table named TEMP1. The INSERT statement then inserts a single record into this table.

Using this syntax, you must supply a value for each field in the table. If you want to leave a particular field blank, use the NULL keyword. To insert default values into fields (when default values are defined for those fields in the data dictionary), use the DEFAULT keyword. This is shown in the following statements:

INSERT INTO TEMP1 VALUES ('Julie Jones', NULL, NULL, NULL); INSERT INTO TEMP1 VALUES ('Lee Singh', DEFAULT, DEFAULT, DEFAULT);

If you want to insert data into only specific fields, you can follow the table name with a comma-separated list of the fields into which you want to insert data. This list of field names must be enclosed in parentheses. For example, the following query inserts data into the Full Name and Active fields:

INSERT INTO TEMP1 ("Full Name", Active)   VALUES ('Jose Luiz', True) 

If the data you want to insert can be found in another table, you can replace the VALUES list with a subquery. For example, the following query inserts data from the EMPLOYEE table into TEMP1:

INSERT INTO TEMP1 ("Full Name", Active)   SELECT RTRIM("First Name") + ' ' + RTRIM("Last Name"), True    FROM EMPLOYEE   WHERE EMPLOYEE."Department Code" = 108

If the fields in the SELECT list match the type, order, and number of fields in the structure of the table you are inserting into, you omit the list of fields being inserted into. This is demonstrated in the following query:

INSERT INTO TEMP1   SELECT RTRIM("First Name") + ' ' + RTRIM("Last Name"),    1, 1000, True    FROM EMPLOYEE   WHERE EMPLOYEE."Department Code" = 104

Note

You can both create and insert records in a single SELECT statement, as discussed earlier in this chapter in the section “Creating Tables.”

Updating Records

You update records in a table using the UPDATE statement. When you use UPDATE, you must specify the name of the table whose records are being updated, as well as which fields you want to update. For example, the following query updates TEMP1, setting the credit limit field to 500 for all records:

UPDATE TEMP1 SET "Credit Limit" = 500

The preceding statement updates all records in the table. In most cases, however, you will want to update only specific records. To specify which records to update, use a WHERE clause. For example, the following statement sets the Active field to False for those records where the Active field has not been assigned a value:

UPDATE TEMP1 SET Active = False   WHERE Active IS NULL 

Here is another example:

UPDATE TEMP1 SET "Credit Limit" = 0   WHERE Active = False

The UPDATE statement also supports a FROM clause. You use FROM if you are using fields and expressions involving another table to perform the update. For example, the following UPDATE statement changes the Credit Limit field to 10000 for all records in TEMP1 where the full name is associated with an employee record, and that employee’s department code is 108:

UPDATE TEMP1 SET TEMP1."Credit Limit" = 10000   FROM EMPLOYEE Emp INNER JOIN TEMP1 ON   TEMP1."Full Name" =    RTRIM(Emp."First Name") + ' ' + RTRIM(Emp."Last Name")   WHERE Emp."Department Code" = 108

Deleting Records

You delete records from a table using the DELETE statement. With DELETE, you use the optional WHERE clause to define which records to delete. If you omit the WHERE clause, all records are deleted from the table.

For example, the following query will remove all records from the TEMP1 table where the Active field contains the value False:

DELETE FROM TEMP1   WHERE Active = False

You can use a subquery to delete records from a table based on values in another table. For example, the following DELETE statement removes all records from TEMP1 where the Full Name field is associated with an employee whose department code is 104:

DELETE FROM TEMP1   WHERE "Full Name" IN    (SELECT RTRIM(Emp."First Name") + ' ' + RTRIM(Emp."Last Name")   FROM EMPLOYEE Emp   WHERE Emp."Department Code" = 104)