Using DML Statements

Data Manipulation Language (DML) statements are the most frequently used statements in an ESQL program. The SELECT statement, also called a query statement, is one type of DML statement. You use a SELECT statement to retrieve data from a database. See Chapter 7, “Query Statements,” for information on how to use SELECT statements.

You use DML statements to change data in a database in one of three ways:

• INSERT — Add one or more rows to a table.

• DELETE — Delete one or more rows from a table.

• UPDATE — Modify the data in one or more rows of a table.

Use DML statements to modify only one table at a time. You can reference host variables in a DML statement. The INSERT, UPDATE, and DELETE statements are discussed in more detail in following sections.

6.1.1

INSERT Rows into a Table

An INSERT statement adds one or more rows to an existing table.

EXAMPLE

The following INSERT statement adds new customer ’Nyquist’ to the customer table:

INSERT a Single Row into a Table

When you use an INSERT statement in an ESQL program, you can use host variables to supply the values. The columns of the customer table listed in the INSERT statement are referred to as the column list. The host variables specified in the VALUES clause of the INSERT statement

EXEC SQL

INSERT INTO customer

(cust_no, last_name, street, city, state) VALUES

SQL-92 Data Manipulation Statements

6–3

EXAMPLE

In the next example, the values for the host variables are determined with program logic, and inserted into the customer table using the INSERT statement:

EXEC SQL BEGIN DECLARE SECTION ; long cust_no_v ;

char name_v [20] ; char street_v [40] ; char city_v [10] ; char state_v [2] ; EXEC SQL END DECLARE SECTION ;

/* Connect to the default database */ EXEC SQL CONNECT TO DEFAULT ;

/* Assign values for input host variables */ cust_no_v = 1006 ;

strcpy (name_v, "Nyquist") ;

strcpy (street_v, "1 Perpetual Street") ; strcpy (city_v, "St. Cloud") ;

strcpy (state_v, "MN") ; EXEC SQL

INSERT INTO customer

(cust_no, last_name, street, city, state) VALUES

(:cust_no_v, :name_v, :street_v, :city_v, :state_v) ; if (sqlca.sqlcode < 0)

{

printf ("Insert statement failed (%ld : %s). \n", sqlca.sqlcode, sqlca.sqlerrm);

EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1);

}

/* Success; commit the insert operation */ EXEC SQL COMMIT WORK ;

printf ("Inserted one row \n"); /*

** Disconnect from the default database */

INSERT Multiple Rows into a Table

To INSERT more than one row, execute an INSERT statement with a sub-query.

EXAMPLE

The following code fragment shows how to INSERT multiple rows from the customer table into the mn_customer table:

The mn_customer table must already exist before you can INSERT rows. The SELECT query expression option allows you to INSERT multiple rows at a time. The query expression must successfully return values for all the columns in the INSERT statement for the operation to succeed.

EXEC SQL

CREATE TABLE mn_customer ( cust_no INTEGER, last_name CHAR(20), street CHAR(40), city CHAR(15), state CHAR(2) ); EXEC SQL

INSERT INTO mn_customer

(cust_no, last_name, street, city, state) SELECT cust_no, last_name, street, city, state FROM customer

SQL-92 Data Manipulation Statements

6–5

6.1.2

DELETE Rows from a Table

A DELETE statement deletes one or more rows from an existing table, depending on the selection criteria in the WHERE clause.

CAUTION: If you do not specify a WHERE clause, the DELETE statement deletes all rows from the table.

EXAMPLE

The following code fragment shows how to use a DELETE statement to delete a row from the customer table. The program uses the host variable cust_no_v to match a row with cust_no 1005, and deletes the row from the table. If the cust_no column is a primary key or is a candidate key, one row is deleted. If there are multiple rows with cust_no = 1005, multiple rows are deleted:

/* CONNECT to the DEFAULT database */ EXEC SQL CONNECT TO DEFAULT ;

/* Get value for cust_no_v */ cust_no_v = 1005 ;

EXEC SQL DELETE

FROM customer

WHERE cust_no = :cust_no_v; if (sqlca.sqlcode < 0)

{

printf ("DELETE statement failed (%ld : %s)\n", sqlca.sqlcode, sqlca.sqlerrm);

EXEC SQL ROLLBACK WORK ; EXEC SQL DISCONNECT DEFAULT ; exit (1);

}

/* Successful; commit the delete operation.*/ EXEC SQL COMMIT WORK ;

printf ("Deleted one row \n\n");

/* DISCONNECT from the DEFAULT database */ EXEC SQL DISCONNECT DEFAULT ;

A DELETE operates on more than one row if the WHERE clause matches multiple rows.

EXAMPLE

The following example deletes any rows from the orders table where the value in the order_date column is less than 2/2/1999:

EXEC SQL DELETE FROM orders

SQL-92 Data Manipulation Statements

6–7

6.1.3

UPDATE Rows in a Table

An UPDATE statement modifies data in one or more rows of a table.

EXAMPLE

The following UPDATE statement updates the phone number of a row in the customer table:

EXAMPLE

The following UPDATE statement gives a 10 percent increase in salary to all employees of department 12. Note that multiple rows are updated with this UPDATE statement:

/*

** Connect to the default database. */

EXEC SQL CONNECT TO DEFAULT ; cust_no_v = 1004 ;

EXEC SQL

UPDATE customer

SET phone = ‘(203)555-2703’ WHERE cust_no = :cust_no_v ; if (sqlca.sqlcode < 0)

{

printf ("Update statement failed (%ld : %s)\n", sqlca.sqlcode, sqlca.sqlerrm); EXEC SQL ROLLBACK WORK ;

EXEC SQL DISCONNECT DEFAULT ; exit (1);

} /*

** Successful; COMMIT the UPDATE operation. */

EXEC SQL COMMIT WORK ;

printf ("Update Successful\n\n"); /* Disconnect from the database */ EXEC SQL DISCONNECT DEFAULT ;

EXEC SQL

UPDATE employee SET sal = sal * 1.1 WHERE deptno = 12 ;