SQL

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

SQL

Data Entry and Modification

Data in SIR/XS databases or tables may be entered, modified or deleted with SQL. There are four commands available to do this. Three commands operate on sets of data in a similar way to SELECT with a WHERE clause. These are:

DELETE FROM

Deletes rows or records. INSERT INTO

SELECT

UPDATE

Modifies values in existing rows and records

The fourth command creates single records or rows interactively:

ENTER INTO

Prompts interactively for new records or rows data to add to the database or table.

DELETE FROM

DELETE FROM    [tabfile.] | [database.] name
               [WHERE condition]
               [COMPILE_ONLY]

Deletes rows or views from tables and records from databases.

Specify record name, record number or the keyword CIR as a database record name. If a database name is not specified, the default database is used. Specify a table or a view and the tabfile name. If the tabfile name is not specified, the default tabfile is used.

WHERE

WHERE

COMPILE_ONLY

Compiles the command and checks for syntax errors but does not perform the deletions.

ENTER INTO

ENTER INTO [tabfile.] | [database.] name
           [ LABELS ]
           [ LENGTH n ]

Prompts for data from the screen to enter new records or rows interactively. It is recommended that one of the other SIR/XS facilities such as FORMS or VisualPQL is used for interactive data entry for all but the most trivial instances.

SQL prompts for the values of each variable. After the last value for one record or row, SQL prompts again for an entry into the first variable of the next record or row. Finish input at any time with the Cancel button. This cancels the current record and terminates the process.

ENTER INTO creates new records or rows. It does not allow the updating of existing data. If a record with the same key as an existing record is entered, it is rejected. If a row with the same values in a unique index as an existing row is entered, it is rejected. If a record or row is rejected the process is terminated.

LABELS

Specifies that the variable label is used as the prompt in place of the variable name and data type.

LENGTH

Specifies the length of the prompt in characters. Prompts are padded or truncated to the specified length. Maximum length is 32 characters.

INSERT INTO

INSERT INTO [tabfile.] | [database.] name
            [(variable list, ...)]
            VALUES (value_list) | SELECT statement
            [COMPILE_ONLY]

Inserts rows into tables and records into databases. The inserted rows or records are created by the SELECT clause.

The variable list specifies the variables or columns. This must match the columns created by the SELECT. If the variable list is omitted, all variables defined for the record or row are expected.

VALUES

The value list is a list of constants to insert. This creates one new record or row with the specified values. The values must correspond in type, length and order with the INSERT INTO variable list. Specify strings in single quotes.

SELECT

SELECT

INSERT INTO

COMPILE_ONLY

INSERT

Example: Suppose a table has been created from the employee database with the following SELECT, and some new employees are to be added:

SELECT * FROM EMPLOYEE ON MYTABLE WHERE GENDER EQ 1
INSERT INTO MYTABLE -
SELECT * FROM EMPLOYEE WHERE GENDER EQ 2

UPDATE

UPDATE [ tabfile.] | [ database.]  name
       SET variable_name = expression, ...
       [ WHERE logical condition]
       [ COMPILE_ONLY ]

Updates existing rows or records in a table or database. Specify record name, record number or the keyword CIR as a database record name. If a database name is not specified, the default database is used. Specify a table or a view and the tabfile name. If the tabfile name is not specified, the default tabfile is used.

The SET statement lists the variables to update. Separate each variable name or expression with a comma.

The WHERE clause specifies the rows or records to be updated. COMPILE_ONLY

UPDATE

Example: To give everyone a 10% raise in Salary and to change date to today:

UPDATE MYTABLE SET SALARY = SALARY * 1.1, CURRDATE = TODAY(0)