Syntax of UPDATE statements

Informix^® allows a specific syntax for UPDATE statements:

UPDATE table SET ( <col-list> ) = (<val-list> )

Genero db supports this syntax.

BDL programs can have the following type of statements:

UPDATE table SET table.* = myrecord.*
UPDATE table SET * = myrecord.*

Static UPDATE statements using this syntax are converted by the compiler to the standard form:

UPDATE table SET column=value [,...]

Note:

With Genero db 3.81, correlated sub-queries cannot be used for multi-column assignment in an UPDATE statement:

UPDATE main_table SET (main_col1, main_col2)
  = ( SELECT sub_col1, sub_col2
      FROM sub_table
      WHERE sub_table.mkey = main_table.key )

Solution

Regular UPDATE statements without sub-queries do not need to be modified. However, you should check your code for UPDATE statements with sub-selects using multiple columns: Such statements need to be split to use only one column at the time.

Replace:

UPDATE main_table SET (main_col1, main_col2) = ( SELECT sub_col1, sub_col2 FROM sub_table WHERE sub_table.mkey = main_table.key )

With:

UPDATE main_table SET (main_col1)
  = ( SELECT sub_col1
      FROM sub_table
      WHERE sub_table.mkey = main_table.key )

UPDATE main_table SET (main_col2)
  = ( SELECT sub_col2
      FROM sub_table
      WHERE sub_table.mkey = main_table.key )