INSERTUPDATEDELETE

INSERTUPDATEDELETE is a term used in data management to describe the coordinated application of insert, update, and delete operations to maintain or synchronize the state of records across databases or data stores. It encompasses the typical DML actions used when reconciling two datasets, applying changes from a source system to a target system, or implementing a complete overwrite of a dataset during ETL and data integration tasks.

In relational databases, these actions may be implemented individually within a single transaction, or more commonly via a MERGE statement or equivalent upsert logic that handles the three cases in one operation. In many ETL pipelines, a change set is generated that includes new rows to insert, existing rows to update, and stale rows to delete, and the target is updated accordingly.

Use cases include data synchronization between operational systems and data warehouses, replication, maintaining audit trails, and integrating streams in near real-time. It is important to model keys and constraints, decide on soft deletes vs hard deletes, and consider idempotency and conflict resolution.

Challenges include ensuring atomicity, handling concurrent updates, and avoiding partial application that can leave the target in an inconsistent state. Performance concerns may arise when applying large numbers of changes, and special care is needed for referential integrity and cascading deletes.

Related concepts include UPSERT (insert or update), MERGE, and change data capture (CDC). The term INSERTUPDATEDELETE emphasizes the full spectrum of operations rather than a single database operator.