Adding/Modifying/Deleting Data Based on the Identifier Field

Application Scenario

• You want to add, update, or delete the target table data based on data filtered from the source table (without an identifier field).

• You want to add, update, or delete target table data based on the identifier field and its value in the source table.

Function Description

You can set Write Method in the Data Synchronization node to Add/Modify/Delete Data Based on Identifier Field to realize the above scenarios.

Note:

1. This document applies to FineDataLink 4.0.28 and later versions. The schemes are not suitable for logical deletion purposes.

2. The schemes in this document are only applicable to the above scenarios. For other data adding, deletion, or modification scenarios, see Overview of Data Synchronization Schemes.

You can download the example data: Order Detail.xlsx

Procedure: Obtain the data with empty unit prices in the Order_Detail table through filtering and delete it.

Configuring the Data Source

1. Create a scheduled task and drag the Data Synchronization node onto the page.

2. Obtain data whose Unit Price value is empty in the Order_Detail table, as shown in the following figure.

Configuring the Data Destination and Mapping

Set Target Table to Order_Detail on the Data Destination and Mapping tab page, as shown in the following figure.

Configuring the Write Method

Select Add/Modify/Delete Data Based on Identifier Field as Write Method, select Delete only, and select Physical Deletion. With no field being selected as Identifier Field, all read data will be added, updated, or deleted. Click Select beside Primary Key Mapping and set the logical primary key to Order ID, as shown in the following figure.

Effect Display

1. Click Run to execute the task. The running result in Log upon successful execution is shown in the following figure.

2. Select Order_Detail on the Data Source tab page in the Data Synchronization node and click Data Preview. You can see that data with an empty unit price value in the Order_Detail table is deleted.

Description: Use the identifier field and its value in the source table to add, update, or delete target table data.

Scenario Description

You can download the example data: Product.xlsx Product Data.xlsx

The source table Product includes an identifier field Status, whose values include Hot-selling (indicating that the record needs to be added), Normal (indicating that the record needs to be deleted), and Viral (indicating that the record needs to be updated).

In the Status column in the Product table, the data whose Product ID value is 15 is marked as Hot-selling, whose Product ID value is 16 is marked as Normal, and whose Product ID value is 14 is marked as Viral. You want to change the data in the target table Product_Data accordingly. 

Note:

Data is physically deleted from the target table in the examples in this document. If you want to perform a logical deletion, that is, to mark data as deleted without removing it from the target table, you need to add a Status column to the target table to track data changes.

Configuring the Data Source

1. Create a scheduled task and drag the Data Synchronization node onto the page.

2. Read data from the source table Product on the Data Source tab page, as shown in the following figure.

Configuring the Data Destination and Mapping

1. Select Product_Data as Target Table, as shown in the following figure.

2. Unmap the Status field, as the Status column does not need to be synchronized to the target table, as shown in the following figure.

Configuring the Write Method

Select Add/Modify/Delete Data Based on Identifier Field as Write Method. Set Identifier Field to Status, and fill in Hot-selling, Viral, and Normal in the text boxes beside Add, Modify, and Delete in Identifier Value respectively.

Since the target table does not have a physical primary key and the Add and Modify options in Write Method are selected, you must define a logical primary key. Set Product ID as the logical primary key in Primary Key Mapping.

Effect Display

1. Click Run to execute the task. The running result in Log upon successful execution is shown in the following figure.

2. Check data in the target table Product Data, which is shown in the following figure. The data whose Product ID value is 15 is added, whose Product ID value is 16 is deleted, and whose Product ID is 14 is updated.