FOR UPDATE clause

The FOR UPDATE clause locks records for Positioned Updates or Positioned Deletes via SQL cursors. The format is:

FOR UPDATE [OF column_expressions]

column_expressions is a list of field names in the database table that you intend to update, separated by a comma. column_expressions is optional, and is ignored.

SELECT * FROM emp WHERE salary > 20000 FOR UPDATE OF last_name, first_name, salary

When each record is fetched, it is locked. If the record is updated or deleted, the lock is held until you commit the change. Otherwise, the lock is released when you fetch the next record.

SELECT 'CatDog' FROM Salespeople

SELECT 999 FROM Salespeople

SELECT DATE '2021-06-05' FROM Salespeople

SELECT TIME '02:49:03' FROM Salespeople

SELECT TIMESTAMP '2021-06-05 02:49:03' FROM Salespeople

SELECT Company_Name FROM Sales_Data
SELECT DISTINCT Company_Name FROM Sales_Data

SELECT Amount FROM Sales_Data
SELECT DISTINCT Amount FROM Sales_Data

SELECT Date_Sold FROM Sales_Data
SELECT DISTINCT Date_Sold FROM Sales_Data

SELECT Time_Sold FROM Sales_Data
SELECT DISTINCT Time_Sold FROM Sales_Data

SELECT Timestamp_Sold FROM Sales_Data
SELECT DISTINCT Timestamp_Sold FROM Sales_Data

SELECT Company_Brochures FROM Sales_Data
SELECT GETAS(Company_Logo, 'JPEG') FROM Sales_Data

SELECT * FROM Salespeople
SELECT DISTINCT * FROM Salespeople

Notes from the examples

A column is a reference to a field in the FileMaker Pro database file. (The field can contain many distinct values.)

The asterisk (*) wildcard character is shorthand for "everything". For the example SELECT * FROM Salespeople, the result is all the columns in the Salespeople table. For the example SELECT DISTINCT * FROM Salespeople, the result is all the unique rows in the Salespeople table (no duplicates).

• FileMaker software does not store data for empty strings, so the following queries always return no records:

  Copy

  SELECT * FROM test WHERE c =''
  SELECT * FROM test WHERE c <>''

• If you use SELECT with binary data, you must use the GetAs() function to specify the stream to return. See Retrieving the contents of a container field: CAST() function and GetAs() function for more information.

Retrieving the contents of a container field: CAST() function and GetAs() function

You can retrieve file reference information, binary data, or data of a specific file type from a container field.

• To retrieve file reference information from a container field, such as the file path to a file, picture, or video, use the CAST() function with a SELECT statement.

• If file data or JPEG binary data exists, the SELECT statement with GetAs(field name, 'JPEG') retrieves the data in binary form; otherwise, the SELECT statement with field name returns NULL.

SELECT CAST(Company_Brochures AS VARCHAR) FROM Sales_Data

You can use the SELECT statement with the GetAs() function to retrieve the data in binary form in the following ways:

• When you use the GetAs() function with the DEFAULT option, you retrieve the default stream for the container without the need to explicitly define the stream type.

  Example

  Copy

  SELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data

• To retrieve an individual stream type from a container, use the GetAs() function with the file’s type based on how the data was inserted into the container field in FileMaker Pro.

  Examples

  If the data was inserted using the Insert > File command, specify 'FILE' in the GetAs() function.

  Copy

  SELECT GetAs(Company_Brochures, 'FILE') FROM Sales_Data

  If the data was inserted using the Insert > Picture command, drag and drop, or paste from the clipboard, specify one of the file types listed in the following table, for example, 'JPEG'.

  Copy

  SELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons

  File type	Description
  'GIFf'	Graphics Interchange Format
  'JPEG'	Photographic images
  'TIFF'	Raster file format for digital images
  'PDF '	Portable Document Format
  'PNGf'	Bitmap image format