Replace (QUEL)
Replaces values of columns in a database table.
Syntax
[repeat] replace tablename (columnname =
[tablefieldname [[integerexpr]].]fieldname
{, columnname =[tablefieldname[[integerexpr]].]
fieldname})
[where qual]
tablename
Specifies the name of the table in which columns are to be replaced; a 4GL name
columnname
Specifies the name of the column in the database table whose value is being updated; a 4GL name
tablefieldname
Specifies the table field containing the column from which the replacement value are taken
integerexpr
Specifies an expression that evaluates to an integer and that indicates the row number in the table field from which the value for an update is taken. If no integer expression appears in brackets, the current row is assumed.
fieldname
Specifies the name of a simple field (or of a column in a table field) that contains the value being used to update the column in the table. When fieldname is a column in a table field, it must be preceded by the name of the table field and a period, as shown.
qual
Specifies a logical expression that specifies which rows have column values replaced. It cannot contain 4GL names. The qualification function is allowed.
Description
The replace statement updates the values of columns in a database table, using values from simple fields or table-field columns on the current form. A value for updating a column can be any legal expression, such as a calculation involving a field in the form. For maximum efficiency, the replace statement can be repeated, using the repeat option.
• An optional where clause can be included to limit the rows in which the column values are changed.
• The qualification function can be used in the where clause.
• The where clause can be stored in a variable. However, in this case, you cannot use the qualification statement.
• Queries in which the qualification function is used are not suitable for use as repeated queries. The keyword repeat is ignored if you use it with such queries.
Updating a database table with values from many rows in a table field generally involves using the replace
statement in conjunction with the unloadtable statement. The replace is located inside an unloadtable loop. See
Unloadtable.
The replace statement can operate on several rows at a time. Therefore, use care in formulating the where clause so as to avoid unintentionally updating rows.
The reserved word all can be used with either a form name or a table field name. The table must contain a column that corresponds to each field or table-field column that is displayed on the form. The reserved word all is attached to the name of the form or table field, not to the name of the database table. In the case of the table field, all is used to indicate all the columns of the current row or of the row indicated by integerexpr, if there is one.
Examples
Replace values in the Projects table with values from the current form:
repeat replace projects (hours = hours,
duedate = enddate)
where projects.name = name;
Replace values in the Part table with values from the third row of the Partstbl table field in the current form:
replace part (partstbl[3].all)
where part.partno = partstbl[3].partno;
Replace values in the Employee table with values from Empform (fields and columns correspond):
repeat replace employee (empform.all)
where employee.lname = lname
and employee.fname = fname;
Update the Personnel table with a computed value.
replace personnel (salary = sal*1.1)
where personnel.empno = empno;
Update the Personnel table with a computed value; the table name and where clause are stored in variables:
tbl = "personnel";
whereclause = "personnel.empno" = char(empno);
replace :tbl (salary=sal*1.1)
where :whereclause;