SQL Commands

The SQL commands used for simple data manipulation are:



   SELECT

   INSERT

   DELETE

   UPDATE

Refer to the ALLBASE/SQL Reference Manual for the complete syntax and semantics of these commands.

SELECT

In simple data manipulation, you use the SELECT command to retrieve a single row, i.e., a one-row query result. The syntax of the SELECT command that describes a one-row query result is:



   SELECT   SelectList

     INTO   HostVariables

     FROM   TableNames

    WHERE   SearchCondition

Note that the GROUP BY, HAVING, and ORDER BY clauses are not necessary, since these clauses usually describe multiple-row query results.

You may omit the WHERE clause from certain queries when the select list contains only aggregate functions:



   EXEC SQL SELECT  AVG(SALESPRICE)

              INTO :AVGSALESPRICE

              FROM  PURCHDB.PARTS

   END-EXEC.

A WHERE clause may be used, however, to qualify the rows over which the aggregate function is applied:



   EXEC SQL SELECT  AVG(SALESPRICE)

              INTO :AVGSALESPRICE

              FROM  PURCHDB.PARTS

             WHERE  SALESPRICE > :SALESPRICE

   END-EXEC.

If the select list does not contain aggregate functions, a WHERE clause is used to restrict the query result to a single row:



   EXEC SQL SELECT  PARTNAME,  SALESPRICE

              INTO :PARTNAME, :SALESPRICE

              FROM  PURCHDB.PARTS

             WHERE  PARTNUMBER = :PARTNUMBER

   END-EXEC.

Because the host variables that hold query results for a simple SELECT command are not arrays of records, they can hold only a single row. A runtime error occurs when multiple rows qualify for a simple SELECT command. You can test for an SQLCODE value of -10002 to detect this condition:



   WORKING-STORAGE SECTION.

   .

   .

   01  MULTIPLEROWS            PIC S9(9) COMP VALUE -10002.

   .

   .

   .

   PROCEDURE DIVISION.

   .

   .

   .

        The SELECT command is executed here.



        IF SQLCODE = MULTIPLEROWS

           DISPLAY "WARNING:  More than one row qualifies."

When multiple rows qualify but the receiving host variables are not in an array of records and the BULK option is not specified, none of the rows are returned.

When a column named in the WHERE clause has a unique index on it, you can omit testing for multiple-row query results if the column was defined NOT NULL. A unique index prevents the key column(s) from having duplicate values. The following index, for example, ensures that only one row will exist for any part number in PURCHDB.PARTS:



   CREATE UNIQUE INDEX PARTNUMINDEX

          ON PURCHDB.PARTS (PARTNUMBER)

If a key column of a unique index can contain a null value, the unique index ensures that no more than one null value can exist for that column.

Another method of qualifying the rows you want to select is to use the LIKE specification to search for a particular character string pattern.

For example, suppose you want to search for all VendorRemarks that contain a reference to 6%. Since the percent sign (%) happens to be one of the wildcard characters for the LIKE specification, you could use the following SELECT statement specifying the exclamation point (!) as your escape character.



   SELECT * FROM PurchDB.Vendors 

    WHERE VendorRemarks LIKE '%6!%%' ESCAPE '!'

The first and last percent sign character are the wildcard characters. The next to the last percent sign, preceded by an exclamation point, is the percent sign that you want to escape, so that it is actually used in the search pattern for the LIKE clause.

The character following an escape character must be either a wildcard character or the escape character itself. Complete syntax is presented in the .

It is useful to execute the SELECT command before executing the INSERT, DELETE, or UPDATE commands in the following situations:

When an application updates or deletes rows, the SELECT command can retrieve the target data for user verification before the data is changed. This technique minimizes inadvertent data changes:



   The program accepts a part number from the user into a host variable

   named PARTNUMBER, then retrieves a row for that part.



   EXEC SQL SELECT  PARTNUMBER,  BINNUMBER

              INTO :PARTNUMBER, :BINNUMBER

              FROM  PURCHDB.INVENTORY

             WHERE  PARTNUMBER = :PARTNUMBER

   END-EXEC.



   The row is displayed, and the user is asked if the bin number is to be

   changed.  If not, the user is prompted for another part number.  If so,

   the user is prompted for the new bin number, which is accepted into the

   host variable named BINNUMBER.  Then the UPDATE command is executed.

   EXEC SQL UPDATE PURCHDB.INVENTORY

               SET BINNUMBER  = :BINNUMBER

             WHERE PARTNUMBER = :PARTNUMBER

   END-EXEC.

To prohibit the multiple-row changes possible if multiple rows qualify for an UPDATE or DELETE operation, an application can use the SELECT command. If multiple rows qualify for the SELECT operation, the UPDATE or DELETE would not be executed. Alternatively, the user could be advised that multiple rows would be affected and given a choice as to whether to perform the change:



   The program prompts the user for an order number and a vendor part

   number in preparation for allowing the user to change the vendor part

   number.  The following SELECT command determines whether more than one

   line at a time exists on the order for the specified vendor part number:



   EXEC SQL SELECT  ITEMNUMBER

              INTO :ITEMNUMBER

              FROM  PURCHDB.ORDERITEMS

             WHERE  ORDERNUMBER    = :ORDERNUMBER

               AND  VENDPARTNUMBER = :VENDPARTNUMBER

   END-EXEC.

   When more than one row qualifies for this query, the program lets the

   user decide whether to proceed with the update operation.

When an application lets the user INSERT a row that must contain a value higher than an existing value, the SELECT command can identify the highest existing value:



   EXEC SQL SELECT  MAX(ORDERNUMBER)

              INTO :MAXORDERNUMBER

              FROM  PURCHDB.ORDERS

   END-EXEC.

   The program can increment the maximum order number by one, then

   provide the user with the new number and prompt for information

   describing the new order.

INSERT

In simple data manipulation, you use INSERT command syntax to either insert a single row or copy one or more rows into a table from another table.

You use the following syntax of the INSERT command to insert a single row:



   INSERT INTO TableName

               (ColumnNames)

        VALUES (DataValues)

You can omit ColumnNames when you provide values for all columns in the target table:



   EXEC SQL INSERT INTO   PURCHDB.PARTS

                  VALUES (:PARTNUMBER,

                          :PARTNAME   :PARTNAMEIND,

                          :SALESPRICE :SALESPRICEIND)

   END-EXEC.

Remember that when you do include column names but do not name all the columns in the target table, ALLBASE/SQL attempts to insert a null value into each unnamed column. If an unnamed column was defined as NOT NULL, the INSERT command fails.

To copy one or more rows from one or more tables to another table, use the following syntax of the INSERT command:



   INSERT  INTO  TableName

                 (ColumnNames)

         SELECT  SelectList

           FROM  TableNames

          WHERE  SearchCondition1

       GROUP BY  ColumnName

         HAVING  SearchCondition2

Note that the SELECT command embedded in this INSERT command cannot contain an INTO or ORDER BY clause. In addition, any host variables used must be within the WHERE or HAVING clauses.

The following example copies historical data for filled orders into table PurchDB.OldOrders, then deletes rows for these orders from PurchDB.Orders, keeping that table minimal in size.



   The INSERT command copies rows from PURCHDB.ORDERS to PURCHDB.OLDORDERS.



   EXEC SQL INSERT  INTO  PURCHDB.OLDORDERS

                          (OLDORDER, OLDVENDOR, OLDDATE)

                  SELECT  ORDERNUMBER, VENDORNUMBER, ORDERDATE

                    FROM  PURCHDB.ORDERS

                   WHERE  ORDERNUMBER = :ORDERNUMBER

   END-EXEC.



   Then the DELETE command deletes rows from PURCHDB.ORDERS:



   EXEC SQL DELETE  FROM  PURCHDB.ORDERS

                   WHERE  ORDERNUMBER: = ORDERNUMBER

   END-EXEC.

UPDATE

In simple data manipulation, you use UPDATE command syntax to change data in one or more columns:



   UPDATE TableName

      SET Columname = ColumnValue

          [,...]

    WHERE SearchCondition

As in the case of the DELETE command, if you omit the WHERE clause, the value of any column specified is changed in all rows of the table.

If the WHERE clause is specified, all rows satisfying the search condition are changed, for example:



   EXEC SQL UPDATE PURCHDB.VENDORS

               SET CONTACTNAME   = :CONTACTNAME :CONTACTNAMEIND,

                   VENDORSTREET  = :VENDORSTREET,

                   VENDORCITY    = :VENDORCITY,

                   VENDORSTATE   = :VENDORSTATE,

                   VENDORZIPCODE = :VENDORZIPCODE

             WHERE VENDORNUMBER  = :VENDORNUMBER

   END-EXEC.

In this example, column CONTACTNAME can contain a null value. To insert a null value, the program must assign a number less than 0 to the indicator variable for this column, CONTACTNAMEIND:



   The program prompts the user for new values for the four columns.



   PROCEDURE DIVISION.

   .

   .

   DISPLAY "Enter Vendor Street> ".

   ACCEPT VENDORSTREET FREE.

   DISPLAY  "Enter Vendor City> ".

   ACCEPT VENDORCITY FREE.

   DISPLAY "Enter Vendor State> ".

   ACCEPT VENDORSTATE FREE.

   DISPLAY "Enter Vendor Zip Code> ".

   ACCEPT VENDORZIPCODE FREE.



   DISPLAY "Enter Contact Name (0 for null)> "

   ACCEPT CONTACTNAME FREE.



     If the user enters a 0 to assign a null value to column

     ContactName, the program assigns a -1 to the indicator

     variable; otherwise, the program assigns a 0 to this variable:



   IF CONTACTNAME = '0' THEN

      MOVE -1 TO CONTACTNAMEIND

   ELSE

      MOVE ZERO TO CONTACTNAMEIND.

DELETE

In simple data manipulation, you use DELETE command syntax to delete one or more rows from a table:



   DELETE FROM  TableName

         WHERE  SearchCondition

The WHERE clause specifies a SearchCondition that rows must meet to be deleted, for example:



   EXEC SQL DELETE FROM PURCHDB.ORDERS

                  WHERE ORDERDATE < :ORDERDATE

   END-EXEC.

If the WHERE clause is omitted, all rows in the table are deleted.

SQL Commands

Technical documentation

» Table of Contents

» Index

SELECT

INSERT

UPDATE

DELETE