Skip to main content

DB_Table.delete_rows

delete_rowskey_values_to_delete key_columns allow_duplicate_matches

Group: Output

Documentation

Removes rows from a database table. It returns the count of the rows that have been deleted. 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

  • key_values_to_delete: a table that contains the values of the primary key columns of the rows to delete. The table must have all columns specified as key_columns. Any other columns are ignored.
  • key_columns: the names of the columns that are used to correlate rows from key_values_to_delete with rows in the target table. Defaults to the primary key of the target table. If no primary key is set, this argument is required.
  • allow_duplicate_matches: specifies if the key values are expected to match more than one row each. Defaults to False, meaning that if a key value matches more than one row, an error will be reported and no rows will be deleted. This setting is meant to prevent additional deletion of too many rows.

Examples

Remove students that have graduated from the students table.

      students.delete_rows graduated_students key_columns=["student_id"]

Errors

  • If the provided primary key columns are not present in the source or target table, Missing_Input_Columns error is raised.
  • If allow_duplicate_matches is set to False (the default) and a key value matches more than one row, a Multiple_Target_Rows_Matched_For_Update error is raised.
  • An SQL_Error may be reported if there is a failure on the database side.

If an error has been raised, the table is not modified (that may not always apply to SQL_Error).

Note that it is possible for a row in key_values_to_delete to have no corresponding rows in the target table. That does not cause any error. This can be detected by checking the returned count of deleted rows.

Remarks

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, and returns the count of rows that would have been deleted by this operation, with a Dry_Run_Operation warning attached.