Changes data in EntitySets, relationships, forms, or sets.
Syntax
UPDATE [ALL] [SQLsetspec] SET «field = value »
[WHERE clause [EVALUATE clause] [-> clause]
Parameters
| ALL | Optional. Regardless of whether ALL is entered, all records in the set specification that meet the specified condition (if any) are updated. |
| SQLsetspec | An SQL set specification. Can contain application documents, EntitySets, relationships, forms, and sets. Role names are permitted.
If SQLsetspec is omitted, the current set is used (if it exists). Application documents named in the SQLsetspec may not be updated. |
| field | A target field in SQLsetspec. |
| value | Can be:
Any value expression.
The keyword NULL.
Field is assigned either the value of the expression, or the $Null property, as specified.
Any number of field = value assignments may be entered. If a field is not explicitly specified for update, its value remains unchanged. |
Comments
The function of the UPDATE command is similar to that of the CHANGE command. UPDATE is able to update more than one database file in one command.
Example
update Employees set Salary = Salary * 1.1
change all Employees let Salary = Salary * 1.1
The preceding two commands are equivalent. Notice that, although the ALL has been omitted from the UPDATE command, it nevertheless updates all records that conform to the set specification.
update Employees, Departments
set Employees.Salary = Departments.StdSalary
where Employees.DNo = Departments.DNo
In the preceding command, the join condition appears in the WHERE clause. Notice that no relationship is needed.
See Also
ADD
CHANGE
Conventions
Adds a single record to EntitySets or relationships ( ISQL option only).
Syntax
INSERT INTO object [( field)] VALUES(expr1 | NULL)
[EVALUATE clause] [clause]
Parameters
| object | The name of an EntitySet or relationship with fields to which you want to add a record. A role name can be used. |
| field | A target field in object. If more than one field is used, the names must be separated from one another by commas. If the field list is omitted, values are assigned to the fields in the order defined in the Object Dictionary.
The number of values must not exceed the number of fields. If field is omitted, it is an error to specify more values than there are fields in object. |
| expr1 | An expression whose value is to be assigned to the corresponding field in the field list; or, if the field list is omitted, to the corresponding field in the Object Dictionary sequence.
Expressions or simple constants can be used. Any number of values can be given, separated from one another by commas. If field is omitted, it is an error to specify more values than there are fields in object.
Fields not explicitly assigned a value become $Null or take their default values. A required field that has no default value must explicitly be assigned a value (unless a SET CHECKNULLS OFF command has been issued). |
| NULL | This value explicitly sets the corresponding field to $Null. |
Comments
INSERT adds a single record to the specified object.
Example
insert into Employees ( LastName, Salary) values (“Smith”, 25000)
add Employees let LastName = “Smith” Salary = 25000
The above examples are equivalent.
The following example adds a new employee record for Fred Jones, age 36, whose salary is $30,000.
insert into Employees values (“Jones”, “Fred”, 36, 30000)
See Also
ADD
Conventions
SET CHECKNULLS
Lists selected data from the database.
Syntax
SELECT [ALL] [DISTINCT] values FROM SQLsetspec [-> setname]
Parameters
| ALL | An optional keyword indicating that all records of the specified type are to be listed. But, even if ALL is omitted, all records are processed by default. |
| DISTINCT | Specifies that only the records that contain unique values in the fields being displayed are to be listed. |
| values | Can be:
*
An asterisk indicates that all fields in the listed records are to be displayed.
«expr »
One or more expressions whose values are to be displayed for each listed record. Each expression must be separated from the next by a comma. |
| SQLsetspec | An SQL set specification. |
Comments
The SELECT command is the SQL equivalent of the LIST command.
You can include sub-queries using the SELECT command in SQLsetspec.
Example
select LastName,FirstName from Employees,Departments
where Employees.DNo = Departments.DNo
list Employees WorkIn Departments format LastName FirstName
The preceding two commands produce equivalent results.
select $max(Salary) from Employees group by DNo
having $average(Salary) > 30000
In the preceding command, the keywords GROUP BY and HAVING produce a list of the highest-salaried employees within each department number where the average salary is greater than $30,000.
select * from Emps order by LastName
The preceding command lists all the fields in each employee record. The records are listed in alphabetical order by last name.
select distinct LastName,FirstName from Emps
The preceding command lists the first and last names of all employees, eliminating duplicates where two or more employees have exactly the same first and last name.
See Also
Conventions
DELETE FROM
FIND
INSERT
LIST
UPDATE
WHERE
States a condition.
Syntax
WHERE expression
Parameters
| expression | A logic expression using conditional and Boolean operators. |
Comments
WHERE states the conditions that restrict processing in the main command to only certain members of the set specification, contingent on a value within each of the (related) records being considered.
Of all the available (related) records in the record-containing objects, only those records for which the condition is logically true become members of the set.
Example
list all Employees where LastName = Smith and FirstName = J?
The preceding command lists only those employees whose last name is Smith and whose first name starts with the letter J.
See Also
About Boolean Expressions
About Conditional Expressions
ADD
CHANGE
COMPUTE
DELETE
DELETE FROM
INSERT
LIST
REPORT FROM
SELECT
UPDATE
Deletes records from EntitySets or relationships with fields.
Syntax
DELETE [ALL] FROM SQLsetspec [EVALUATE clause] [-> clause]
Parameters
| ALL | Optional. Whether or not you include the ALL argument, all records in SQLsetspec are deleted. |
| SQLsetspec | An SQL set specification. If omitted, records are deleted from the current set (if it exists). |
Comments
DELETE FROM is the SQL version of the DELETE command.
Records cannot be deleted from a composite set. Data cannot be deleted from application documents or forms.
DELETE FROM acts by default on all records in SQLsetspec. By contrast, DELETE acts by default on only the first record in a set.
Warning: You cannot recover data deleted by the DELETE FROM command. If no backup copy of the data exists, the data is permanently lost.
Example
delete from Employees where LastName = “Smith”
Deletes every employee whose last name is Smith.
delete from Fields where Length > (select avg (length) from Fields)
Deletes every field whose length is greater than the average length of all fields.
See Also
$MemberCount
DELETE
Marks the end of a transaction, discarding the results.
Syntax
ROLLBACK WORK
Comments
The ROLLBACK WORK command is the SQL equivalent of the QUITTRANSACTION command. The value of the system variable $InTransaction indicates if an explicit transaction is in progress; the ROLLBACK WORK command sets $InTransaction to 0 ($False).
See Also
$InTransaction
BEGIN WORK
COMMIT WORK
ENDTRANSACTION
QUITTRANSACTION
SET TRANSACTION FLOW
TRANSACTION
Starts an explicit transaction.
Syntax
BEGIN WORK
Comments
This command is the SQL equivalent of the TRANSACTION command.
See Also
$InTransaction
COMMIT WORK
ROLLBACK WORK
TRANSACTION
Ends a transaction and writes all updates to the database.
Syntax
COMMIT WORK
Comments
This command is the SQL equivalent to the ENDTRANSACTION command.
See Also
$InTransaction
BEGIN WORK
ROLLBACK WORK
TRANSACTION
English 
Portuguese