Search This Blog

Wednesday, August 7, 2013

GREENPLUM: UPDATE SYNTAX EXPLAINATION

ONLY
If specified, update rows from the named table only. When not specified, any tables inheriting from the named table are also processed.
table
The name (optionally schema-qualified) of an existing table.
alias
A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo.
column
The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table’s name in the specification of a target column.
expression
An expression to assign to the column. The expression may use the old values of this and other columns in the table.
DEFAULT
Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).
fromlist
A list of table expressions, allowing columns from other tables to appear in the
WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM clause of a SELECT statement. Note that the target table must not appear in the fromlist, unless you intend a self-join (in which case it must appear with an alias in the fromlist).
condition
An expression that returns a value of type boolean. Only rows for which this expression returns true will be updated.
output_expression
An expression to be computed and returned by the
UPDATE command after each row is updated. The expression may use any column names of the table or table(s) listed in FROM. Write * to return all columns.
output_name
A name to use for a returned column.
 
SET
is not allowed on the Greenplum distribution key columns of a table.

When a
FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row should not join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

No comments:

Post a Comment