UPDATE

Modifies rows of a database table.

Syntax 1:

This is the most standard syntax, working with all type of database engines.

UPDATE table-specification
   SET
       column = { variable | sql-expression }
       [,...] 
   [  sql-condition ]

Syntax 2:

This syntax is not standard, but will be converted by compiler to a portable UPDATE syntax.

UPDATE table-specification 
   SET ( column [,...] )
     = ( { variable | sql-expression } [,...] )
   [  sql-condition ]

Syntax 3:

This syntax is not portable, and is not converted by the compiler.

UPDATE table-specification 
   SET [table.]*
     = ( { variable | sql-expression } [,...] )
   [  sql-condition ]

Syntax 4:

The last syntax requires a database schema specification with SCHEMA instruction, and the corresponding database schema file.

UPDATE table-specification 
   SET { [table.]* | ( column [,...] ) }
     = record.*
   [  sql-condition ]

where table-specification is:

[dbname[@dbserver]:][owner.]table

And sql-condition is:

WHERE { condition | CURRENT OF cursor }

dbname identifies the database name.
dbserver identifies the database server (INFORMIXSERVER).
owner identifies the owner of the table, with optional double quotes.
table is the name of the database table.
column is a name of a table column.
variable is a program variable, a record member or an array member used as a parameter buffer to provide values.
sql-expression is an expression supported by the database server, this can be a literal or NULL for example.
record is the name of a record (followed by dot star in this syntax).
condition is an SQL expression to select the rows to be updated.
cursor is the identifier of a database cursor.

Usage

The UPDATE SQL statement can be used to modify one or more rows in the specified database table.

The dbname, dbserver and owner prefix of the table name should be avoided for maximum SQL portability.

The third syntax should be avoided, this syntax is not standard and will not work will all database types.

The fourth syntax can be used if the database schema file has been generated with the correct data types. This is especially important when using SERIAL columns or equivalent auto-incremented columns. The fglcomp compiler will automatically extend the SQL text with the columns identified by the record variable. The columns defined in the database schema file as SERIAL (code 262) will be omitted in the generated column list.

column with a subscript expression (column[a,b]) is not recommended because most database servers do not support this notation.

For more details about the WHERE CURRENT OF clause, see Positioned updates/deletes.

Example

MAIN
   DEFINE myrec RECORD
            key INTEGER,
            name CHAR(10),
            cdate DATE,
            comment VARCHAR(50)
         END RECORD
   DATABASE stock 
   LET myrec.key     = 123
   LET myrec.name    = "Katos"
   LET myrec.cdate   = TODAY
   LET myrec.comment = "xxxxxx"
   UPDATE items SET
        name    = myrec.name,
        cdate   = myrec.cdate,
        comment = myrec.comment 
    WHERE key = myrec.key 
END MAIN