User Guide : Designing and Executing Map : Setting Up Target Data : Defining Update and Delete Key Options
 
Share this page                  
Defining Update and Delete Key Options
You can specify the update and delete options for the target data if you have selected the target connection output mode as Update or Delete, respectively and the chosen connector is dBase, SQL, or ODBC.
If your target application has primary key definition capability, Map Editor automatically reads the primary key definition from the target database.
If your target has no primary key, the field name in Fields to Match by default uses the first defined field within that file. To change it, click the default name, and choose another field from the list.Before defining the update and delete keys, make sure that at least one expression is defined in the target.
To add update and delete key options:
1. In the map file, go to the Targets tab > Define Update and Delete Key Options section.
2. Click /download/attachments/24975459/Map_Source_Add_Icon.png?version=1&modificationDate=1490852595364&api=v2 to add target data fields to match.
The Add Target Fields to Match dialog box is displayed.
3. Select the target fields to match the records for the selected target mode (update or delete) and click Add.
4. If you have selected the Target Mode as Update:
Select an Update Mode Option:
Update FIRST matching record and insert nonmatching records – This option updates only the first match of any duplicate records. It also adds to the end of the target table any records from the source that have key fields that do not match any existing records in the target table.
Update FIRST matching record and ignore nonmatching records – This option updates only the first match of any duplicate records and ignores any records in the source that have key fields that do not match up with any existing records in the target table.
Update ALL matching records and insert nonmatching records – This option looks up all records that match the key field and update them. It also adds to the end of the target table any records from the source that have key fields that do not match any existing records in the target table. This is the default in Update mode.
Update ALL matching records and ignore nonmatching records – This option looks up all records that match the key field and update them, and ignores any records in the source that have key fields that do not match any existing records in the target table.
Insert ONLY nonmatching records – This option adds to the end of the target table any records from the source that have key fields that do not match any existing records in the target table. This option does not update any target records that match.
Select an Update Fields option:
Update only mapped fields – This option replaces only the data in fields that are mapped on the Map All tab with the corresponding data in the source records with matching key fields. All unmapped fields (fields with no information in the Target Field Expression column) are not affected. This is the default in Update mode.
Update ALL fields – This selection deletes the target record when a key field match is found and replaces the complete record with data from the corresponding source record.
To insert null values in target fields when you have null fields in the source data, even if there is existing data in the target field, select the Allow null values to overwrite data in target fields check box.
Note:  This option overwrites the data in all fields, even if you chose Update Only Mapped Fields in the Update Mode Options section.
5. If you have selected the Target Mode as Delete:
Delete only FIRST record match found –This option only deletes the first target record found with the matching key field.
Delete ALL record matches found – This option deletes all target records that have a matching key field. This is the default in Delete Mode.
6. Save the map file.