Skip to main content

DB_Table.update_rows

update_rowssource_table update_action key_columns error_on_missing_columns on_problems

Group: Output

Documentation​

Updates the database table with the contents of the provided table. This operation will only work if this table is a 'trivial' table reference, like returned by Connection.query, so it corresponds directly to a table existing in a Database. If the target table is transformed in any way, this correspondence is lost and the update operation cannot be performed on such a transformed object.

Arguments​

  • source_table: the table containing values for the update. This table may be in-memory (in which case it will be uploaded as a temporary table) or any database query with any desired transformations applied, as long as it is coming from the same Connection as the target table.
  • update_action: specifies the update strategy - how to handle existing new and missing rows.
  • key_columns: the names of the columns to use identify correlate rows from the source table with rows in the target table. This key is used to determine if a row from the source table exists in the target or is a new one.
  • error_on_missing_columns: if set to False (the default), any columns missing from the source table will be left unchanged or initialized with the default value if inserting. If a missing column has no default value, this will trigger a SQL_Error. If set to True, any columns missing from the source will cause an error. - on_problems: the behavior to use when encountering non-fatal problems.

Errors​

  • If key_columns are not present in either the source or target tables, a Missing_Input_Columns error is raised.
  • If the target table does not exist, a Table_Not_Found error is raised.
  • If error_on_missing_columns is set to True and a column is missing from the source table, a Missing_Input_Columns error is raised.
  • If the source table contains columns that are not present in the target table, an Unmatched_Columns error is raised.
  • If a column in the source table has a type that cannot be trivially widened to the corresponding column in the target table, a Column_Type_Mismatch error is raised. If the types do not match but can be widened, an Inexact_Type_Coercion is reported.
  • If update_action is Insert and a row with the same key already exists in the target table, a Rows_Already_Present error is raised.
  • If the update_action is Update and some rows in the source have no corresponding rows in the target table, a Unmatched_Rows error is raised.
  • If the source table contains multiple rows for the same key, a Non_Unique_Key error is raised.
  • If a row in the source table matches multiple rows in the target table, a Multiple_Target_Rows_Matched_For_Update error is raised.
  • If another database error occurs, an SQL_Error is raised.

If any error was raised, the data in the target table is not modified.

Remarks​

Type Widening​

Smaller types can be widened to a larger type, for example 32-bit integer column can be widened to a 64-bit integer column, but not vice versa (because larger numbers could not fit the smaller type and the type of the column in the target table cannot be changed).

Dry Run if Output disabled​

If performing output actions is disabled, only a dry run is performed and no permanent changes occur. The operation checks for errors like missing columns or mismatched types and if successful, returns the target table unchanged with a Dry_Run_Operation warning attached.

More expensive checks, like clashing keys or unmatched rows are checked only on a sample of rows, so errors may still occur when the output action is enabled.