Publishing only some rows in a table

When no WHERE clause is specified in a publication definition, all changed rows in the publication are uploaded. You can add WHERE clauses to articles in the publication to limit the rows to be uploaded to those that have changed and that satisfy the search condition in the WHERE clause.

The search condition in the WHERE clause can only reference columns that are included in the article. In addition, you cannot use any of the following in the WHERE clause:

  • subqueries

  • variables

  • non-deterministic functions

These conditions are not enforced, but breaking them can lead to unexpected results. Any errors relating to the WHERE clause are generated when the DML is run against the table referred to by the WHERE clause, and not when the publication is defined.

To create a publication using a WHERE clause (Sybase Central Admin mode)
  1. Connect to the remote database as a user with DBA authority using the SQL Anywhere plug-in.

  2. Open the Publications folder.

  3. Choose File » New » Publication.

  4. In the What Do You Want To Name The New Publication field, enter a name for the new publication. Click Next.

  5. Click Next.

  6. On the Available Tables list, select a table. Click Add.

  7. Click Next.

  8. Click Next.

  9. In the Articles List, select a table and enter the search condition in the The Selected Article Has the following WHERE clause pane.

  10. Click Finish.

To create a publication using a WHERE clause (SQL)
  1. Connect to the remote database as a user with DBA authority.

  2. Execute a CREATE PUBLICATION statement that includes the tables you want to include in the publication and a WHERE condition.

    See CREATE PUBLICATION statement [MobiLink] [SQL Remote].

Example

The following example creates a publication that includes the entire employees table and all rows in the SalesOrder table that have not been marked as archived.

CREATE PUBLICATION main_publication ( 
TABLE Employees, 
TABLE SalesOrders
WHERE archived = 'N' 
);

By changing the archived column in the table from any other value to an N, a delete is sent to the MobiLink server during the next synchronization. Conversely, by changing the archived column from N to any other value, an insert is sent. The update to the archived column is not sent to the MobiLink server.