This guide will help you learn how to update records in a table, e.g. overwrite only the entries that have been changed.

Modes available

Transformations can be run in one of the four modes:

Insert (available in all connectors)
Update (available in all connectors)
Synchronization (available in the connector for Salesforce CRM)
Delete (available in all JDBC connectors: AS400, DB2, DBase, EnterpriseDB, Firebird, MS Access, MS SQL, MySQL, ODBC, OpenBravo, OpenEdge, Oracle, PostgreSQL, SyBASE, and Vertica)

Configuration

To update a table, you need to open the output database connector and switch it to the Update mode during configuring the connector's properties. You can do this when the list of the source data fields is being displayed.

If you select Update, Synchronization, or Delete mode, you will also need to select a field or set of fields to be used as primary keys. Values of these fields will be used to compare incoming records to the ones that already exist in your data target and define if a record should be updated, added, or deleted.

To make sure the right records are updated/deleted, select the field(s) that has unique values as the primary key. It can be, for example, a field containing some sort of ID that's also contained in your data source, or a combination of fields that together will form a value that's different for each record.

Make sure to have input fields mapped to the field(s) selected as primary key in an operation linked to your target connector.

Difference between modes

Insert mode adds records to your data target regardless of whether such records exist in the target or not.

Update more looks for matching records based on the primary key set, updates existing records that match incoming records, and creates new records if no match was found.

Synchronization looks for matching records in the target. Then if there's a record in data target that matches an incoming record, it's updated; if an incoming record doesn't match any record that's already contained in your data target, a new record is created; if there's a record in your data target that doesn't match any incoming record, such record is deleted from your data target.

To synchronize data using connectors that don't have synchronization mode enabled, you can select Insert mode and tick 'Clear the selected table before any data written' at the last screen of your target connector. What will happen in this case is that all records that are already there in your target table will be deleted and records from your data source will be inserted. This way you will have 100% match between data in your source and target

Delete mode looks for matching records based on the primary key set and deletes them from your data target.

In the examples below "Company" is the field set as primary key:

Source table:

No.	Company	FirstName	LastName	Email	Phone	Title
1	ABC	John	Smith	J@abc.com	123	CEO
2	Microsoft	Jim	Black	jim@ms.com		QA Engineer
3	Joomla	Phil	Collins	PC@joo.cn

Target table:

No.	Company	FirstName	LastName	Email	Phone	Title
1	ABC	John	Smith	J@abc.com	123 123 5766
2	IBM	Kate	Adams		235 888 9966
3	Microsoft	Jim	Black	jim@ms.com		QA Engineer

Transformation in the Insert mode:

No.	Company	FirstName	LastName	Email	Phone	Title
1	ABC	John	Smith	J@abc.com	123 123 5766
2	IBM	Kate	Adams		235 888 9966
3	Microsoft	Jim	Black	jim@ms.com		QA Engineer
4	ABC	John	Smith	J@abc.com	123	CEO
5	Microsoft	Jim	Black	jim@ms.com		QA Engineer
6	Joomla	Phil	Collins	PC@joo.cn

(Records from the source table have been added to the target table regardless of where they existed there before or not.)

Transformation in the Update mode:

No.	Company	FirstName	LastName	Email	Phone	Title
1	ABC	John	Smith	J@abc.com	123	CEO
2	IBM	Kate	Adams		235 888 9966
3	Microsoft	Jim	Black	jim@ms.com		QA Engineer
4	Joomla	Phil	Collins	PC@joo.cn

(ABC and Microsoft records have been updated, as it exists in both the source and target tables; Joomla record has been added, as it didn't exist in the target table.)

Transformation in the Synchronization mode:

No.	Company	FirstName	LastName	Email	Phone	Title
1	ABC	John	Smith	J@abc.com	123	CEO
2	Microsoft	Jim	Black	jim@ms.com		QA Engineer
3	Joomla	Phil	Collins	PC@joo.cn

(ABC and Microsoft records have been updated; IBM record has been deleted; and Joomla contact has been added.)

Transformation in the Delete mode:

No.	Company	FirstName	LastName	Email	Phone	Title
2	IBM	Kate	Adams		235 888 9966

(ABC and Microsoft records have been deleted; IBM record has been preserved as there was no matching record in the source table; Joomla record hasn't been created)