Modifying Data Using CA Dataquery › SQL Mode Data Modification

SQL Mode Data Modification

For a CA Dataquery user to use the SQL data maintenance commands, the user must be authorized. This is done on the User Table Maintenance panel or through external security, if CA Dataquery is externally secured.

A brief description of each SQL Mode data manipulation command follows:

DELETE

Allows you to remove one or more rows in a table. The rows to be deleted are chosen by the SEARCH function. An example of the DELETE statement follows:

    DELETE FROM DEPTTBL
    WHERE DEPTNO = 'D1'

INSERT

Adds one or more rows to a table. The data to be inserted can be specified directly in the INSERT statement, or data can be retrieved from an existing table using a subselect statement.

Simple INSERT of one Row
INSERT INTO EMP
    VALUES ('000315', 'JOHN', 'T', 'SMITH',)
Selected INSERT of Multiple Rows
INSERT INTO SMITH.TEMPEMP
    SELECT *
    FROM EMP

UPDATE

Replaces the value of one or more columns within one or more rows of a table with another value. You can use selection criteria similar to that used in a SEARCH statement of a query. This criteria qualifies the rows you want to modify. Use qualification to alleviate the possibility of modifying the wrong rows. An example of the UPDATE statement follows:

    UPDATE CAI_TEMP
    SET PHONENO = '3565'
    WHERE EMPNO = '009123'

If no search condition is specified, CA Dataquery updates all rows in the named table.

Executing an SQL Query

Use the CA Dataquery Editor to write the SQL commands, DELETE, INSERT and UPDATE and follow the usual execution procedures.