SQL Reference
Table Of Contents
- Chapter 1 Introduction
- Chapter 2 Supported standards
- Support for Unicode characters
- SQL statements
- SELECT statement
- SQL clauses
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- UNION operator
- ORDER BY clause
- OFFSET and FETCH FIRST clauses
- FOR UPDATE clause
- DELETE statement
- INSERT statement
- UPDATE statement
- CREATE TABLE statement
- TRUNCATE TABLE statement
- ALTER TABLE statement
- CREATE INDEX statement
- DROP INDEX statement
- SQL expressions
- SQL functions
- FileMaker system objects
- Reserved SQL keywords
- Index
Chapter 2 | Supported standards 19
UPDATE statement
Use the UPDATE statement to change records in a database table. The format of the UPDATE
statement is:
UPDATE table_name SET column_name = expr, ... [ WHERE { conditions } ]
column_name is the name of a column whose value is to be changed. Several columns can be
changed in one statement.
expr is the ne
w value for the column.
Usually the expressions are constan
t values for the columns (but they can also be a subquery).
You must enclose character string values in pairs of single quotation marks ('). To include a single
quotation mark in a character string value enclosed by single quotation marks, use two single
quotation marks together (for example, 'Don''t').
Subqueries must be enclosed in parentheses.
The WHERE
clause is any valid clause. It determines which records are updated.
Example
UPDATE statement on the emp table.
UPDATE emp SET salary=32000, exempt=1 WHERE emp_id = 'E10001'
The UPDATE statement changes every record that meets the conditions in the WHERE clause.
In this case the salary and exempt status are changed for all employees having the employee
ID E10001. Because employee IDs are unique in the Employee table, only one record is
updated.
Example
UPDATE statement on the emp table with a subquery.
UPDATE emp SET salary = (SELECT avg(salary) from emp) WHERE emp_id = 'E10001'
In this case, the salary is changed to the average salary in the company for the employee
having employee ID E10001.
Note In container fields, you can UPDATE with text only, un
less you prepare a parameterized
statement and stream the data from your application. To use binary data, you may simply assign
the filename by enclosing it in single quotation marks or use the PutAs() function. When
specifying the filename, the file type is deduced from the file extension:
UPDATE table_name SET (container_name) = ? AS 'filename.file extension'
Unsupported file types will be inserted as type FILE.
When using the PutA
s() function, specify the type: PutAs(col, 'type'), where the type
value is a supported file type as described in “Retrieving the contents of a container field: CAST()
function and GetAs() function” on page 16.