Difference between revisions of "REPLACE"

Revision as of 10:55, 8 January 2010

Purpose

Modify fields in a table

Syntax

REPLACE [<scope>]

<field> WITH <exp> [, <field> WITH <exp>] | <memo-field> with <exp> [ADDITIVE]

[, <field> WITH <exp>...] | [, <memo-field> with <exp> [ADDITIVE] ...]

[BLANK]

[FROM ARRAY <array-name>]

[FOR<condition>]

[WHILE <condition>]

[REINDEX]

See Also

:=, BROWSE, CHANGE, DELETE, EDIT, READ, PACK, RECALL, SET DELETED, SET DICTIONARY, SET FILTER, SET RELATION, USE, ZAP

Description

The REPLACE command updates fields in the active table. The default <scope> is the current record. The REPLACE command may also update fields in open tables other than the active table. Fields in other tables must be prefixed with an alias pointer, which is an alias name followed by the symbol '->' or '.'. The alias name is the name optionally assigned in the USE command (if no alias is specified, the table basename is used) or the workarea letter.

If SET FILTER TO is in effect, then only those records that satisfy the filter are processed. If SET DELETED ON is in effect, then only those records that are not marked for deletion are processed.

BLANK

If the optional keyword BLANK is used the current record is cleared and replaced into the table. Default settings defined in the Data Dictionary apply to the replaced record.

<memo-field> WITH <expC> [ADDITIVE]

The <memo-field> with <exp> command is used to replace the specified memo field with an expression. If the ADDITIVE keyword is not used, existing memo field contents are overwritten by <exp>. The ADDITIVE keyword is used to append new text to the end of memo contents instead of overwriting the contents of the memo.

FROM ARRAY <array>

The REPLACE FROM ARRAY command allows you to replace fields in the current table with the contents of a previously declared two-dimensional <array> of the specified name. The data types and sizes of elements in the rows of the arrays must correspond to the fields in the table.

FOR <condition>

If the FOR <condition> clause is specified, only those fields in the rows which satisfy the specified <condition> will be replaced. When the FOR clause is used, the <scope> defaults to ALL.

WHILE <condition>

The WHILE <condition> clause can be used to restrict the number of records replaced. When the condition becomes false, the REPLACE operation will stop. If the WHILE condition is used in conjunction with the FIND or SEEK commands on index files, the range of records being replaced can be restricted and performance can be optimized. When the WHILE clause is used, the <scope> defaults to REST.

REINDEX

The REINDEX keyword allows the indexes to be rebuilt upon completion of the command.

The field and the expression data types must be compatible. If the table is indexed, then Recital processes the records in the table in the order as specified in the master index file. When you update fields in indexed files, and if the field being updated is part of the key, then the index file will also be updated automatically. Block replacements on indexed files have undefined results, as the next record keeps moving as the indexes are repositioned. You can overcome this if you issue the command SET ORDER TO 0 before executing REPLACE.

The REPLACE command can be used to update memos from long strings. If the currently selected table is shareable, then the Recital/4GL will automatically lock and then unlock each record in turn as it performs the REPLACE operation. If a replacement requires an index file to be updated, then the Recital/4GL will automatically lock the index file, update it and unlock it. If the record cannot be locked, an error message will be returned. The ON ERROR command can be used to trap this message.

The REPLACE command will evaluate any validation defined in the Applications Data Dictionary and return an error if the validation fails. The error can be trapped with the ON ERROR command. To bypass the Dictionary temporarily, use the SET DICTIONARY TO command.

If SET LOCKTYPE TO OPTIMISTIC is active, an attempt to use the REPLACE command on a record that has been modified since it was last read will generate an error.

Example

use patrons index events, names
replace all date with ctod("26/04/2000");
  for event = "PHANTOM"
 
// Multiple Replace Statements
replace firstname with m_firstname,;
  surname with m_surname,;
  state with m_state

Products

Recital Server, Recital