Replicating UPDATE statements

UPDATE statements might not be replicated exactly as they are entered in the database. The following scenarios describe how an UPDATE statement is replicated:

When an UPDATE statement has the effect of updating a row in a given remote user's subscription, it is sent to that user as an UPDATE statement.
When an UPDATE statement has the effect of removing a row from a given remote user's subscription, it is sent to that user as a DELETE statement.
When an UPDATE statement has the effect of adding a row to a given remote user's subscription, it is sent to that user as an INSERT statement.

To demonstrate how an UPDATE statement can be replicated, the following example uses a consolidated database and three remote databases for the users: Ann, Marc, and ManagerSteve.

A publication, with a Rep column containing Marc and Ann. Each subscriber receives the rows marked with their own name.

On the consolidated database, there is a publication, named cons, that is created with the following statement:

CREATE PUBLICATION "cons"."p1" (
 TABLE "DBA"."customers" ( "ID", "Rep") SUBSCRIBE BY repid
);

Ann and Marc subscribe to the cons publication by their respective Rep column values. ManagerSteve subscribes to the cons publication with both Ann and Marc's Rep column values. The following statements subscribe the three users to the publication cons:

CREATE SUBSCRIPTION 
  TO "cons"."p1"( 'Ann' ) 
  FOR "Ann";
CREATE SUBSCRIPTION 
  TO "cons"."p1"( 'Marc' ) 
  FOR "Marc";
CREATE SUBSCRIPTION 
  TO "cons"."p1"( 'Ann' ) 
  FOR "ManagerSteve";
CREATE SUBSCRIPTION 
  TO "cons"."p1"( 'Marc' ) 
  FOR "ManagerSteve";

On the consolidated database, an UPDATE statement that changes the Rep value of a row from Marc to Ann is replicated to:

Marc as a DELETE statement.
Ann as an INSERT statement.
ManagerSteve as an UPDATE statement.

The contents of the consolidated database and the remote databases after the replication.

This reassignment of rows among subscribers is sometimes called territory realignment because it is a common feature of sales force automation applications, where customers are periodically reassigned among representatives.

Replicating UPDATE statements

See also