join_condition subexpressions that only reference target_table_name columns can affect which action is taken, often in surprising ways.

Only columns from target_table_name that attempt to match data_source rows should appear in join_condition. Join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in the data_source match rows in the target_table_name. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued. source_aliasĪ substitute name for the data source. Refer to the SELECT statement or VALUES statement for a description of the syntax. source_queryĪ query ( SELECT statement or VALUES statement) that supplies the rows to be merged into the target_table_name. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included. If ONLY is not specified, matching rows are also included from any tables inheriting from the named table. If ONLY is specified before the table name, matching rows are included from the named table only.

The name (optionally schema-qualified) of the source table, view, or transition table. For example, given MERGE INTO foo AS f, the remainder of the MERGE statement must refer to this table as f not foo. When an alias is provided, it completely hides the actual name of the table. target_aliasĪ substitute name for the target table. The ONLY keyword and * option do not affect insert actions, which always insert into the named table only. If ONLY is not specified, matching rows are also updated or deleted in any tables inheriting from the named table. If ONLY is specified before the table name, matching rows are updated or deleted in the named table only. The name (optionally schema-qualified) of the target table to merge into. MERGE is not supported if the target_table_name is a materialized view, foreign table, or if it has any rules defined on it. You will require the SELECT privilege on the data_source and any column(s) of the target_table_name referred to in a condition. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are executed. If you specify an delete action, you must have the DELETE privilege on the target_table_name. If you specify an insert action, you must have the INSERT privilege on the target_table_name.

If you specify an update action, you must have the UPDATE privilege on the column(s) of the target_table_name that are referred to in the SET clause. Since actions are evaluated in their specified order, DO NOTHING can be handy to skip non-interesting source rows before more fine-grained handling. When DO NOTHING is specified, the source row is skipped. All actions refer to the target_table_name, though modifications to other tables may be made using triggers. The syntax of those commands is different, notably that there is no WHERE clause and no table name is specified. MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names. No more than one WHEN clause is executed for any candidate change row. For each candidate change row, the first clause to evaluate as true is executed.

For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements.įirst, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. MERGE performs actions that modify rows in the target_table_name, using the data_source.

Postgresql update join table