Update
Updates the values of the columns in a database table.
Syntax
[repeated] update [owner.]tablename [corrname]
[from fromsource {, fromsource}]
set columnname = expression {, columnname =
expression}
[where qual]
[owner.]tablename
Specifies the name of the database table to be updated and, optionally, the owner of the table. The table name can be a 4GL name; however, if a 4GL variable is used, an owner cannot be specified, unless the owner name is included as part of the value. In other words, the variable can contain the value owner.tablename.
corrname
Specifies the correlation name for the table. This can be used in qual or any of the expressions to identify which table a column is to be taken from (for example, corrname.columnname). If corrname is omitted, tablename.columnname can be written.
fromsource
The name of the source of the columns that appear in qual or any of the expressions. A source can be a table or an outer join between tables. A table is specified with the syntax:
[owner.]tablename [corrname]
A join between tables is specified with the syntax:
source join_type join source on
search_condition
where a
source can be a table name or a join. For more information about the from clause, see
Select.
columnname
Specifies the name of a particular column to be updated in the table. A 4GL name.
expression
Specifies a value to which a column is to be set by the update statement.
Expression is typically a constant, a field, a table-field column, or a database expression. Expression can refer to database table columns. Expression can also be built from a function or an arithmetic operation using constants or single-valued fields. In addition, it can be built from a global or local variable, a literal, a record or an array record attribute.
qual
Specifies a logical expression that restricts the query to certain conditions. It cannot include 4GL names. Use the qualification function.
Description
The 4GL update statement modifies the values of columns in a database table with values from the current form or variables. The values can originate from simple fields, a table field, the columns of a table field, record attributes, or attributes of an array record. The syntax for the update statement in 4GL is identical to the SQL version, except that the set clause is modified to accommodate the assigning of values from the form or variables to columns in a database table.
The 4GL update statement updates the columns listed in the set clause for all rows in the table that satisfy the search condition in the where clause.
• The set clause contains column values from the table being updated or any table or tables listed in the from clause.
• The where clause contains a qualification function.
• The where clause can be stored in a variable. However, in this case, you cannot use the qualification function.
Because the update statement can operate on several rows or records at a time, formulate the where clause carefully to avoid unintentional updates.
If you expect to use a query frequently, overall performance improves if you use the repeated clause.
Precede each field name in the set clause with a colon to dereference it and eliminate possible conflicts between the field name or table-field column and the name of a database column. Use a colon to dereference fields in the where clause.
You can use the all option as a suffix with either a form name or a table-field name. The table must have a column that corresponds to each of the displayed fields or table-field columns on the form. When using all:
• Attach the reserved word all to the name of the form or table field, not to the name of the database table.
• In the case of the table field, use all to indicate all the columns of the current row or of the row specified by integerexpr, if there is one.
• The asterisk (*) substitutes for the list of database columns.
Updating a database table with values from many rows in a table field generally involves using the update statement in conjunction with the unloadtable statement. The update statement is located inside an unloadtable loop. See the section Unloadtable for additional information.
Examples
Update rows in the Projects table with values from the current form:
repeated update projects
set hours = :hours, duedate = :enddate
where name = :name;
commit;
Update the Personnel table with a computed value:
update personnel
set sal = :salary * 1.1
where empno = :empno;
commit;
Update row in the Personnel table with values from all the simple fields in form called Deptform:
update personnel
set * = deptform.all
where idnum = :idnum;
commit;
Update the salaries of all employees in job category acc, using the value for a standard raise in the table Dept:
update employee e
from dept d
set salary = d.std_raise * e.salary
where e.jobcat = 'acc'
and d.dname = e.dname;
Update the Employee table to change Address using the value in the newaddr attribute of the record emprec:
update employee
set address = :emprec.newaddr
where name = :emprec.name;
Update the Employee table with changes to employee titles using the value in the title attribute of array emparray:
update employee
set emptitle = :emparray.title
where emptitle = :emparray.old_title;