Selecting different fields and keys for insert, update, and delete

Many applications will write to/delete from the same table in many different ways, using different fields as keys and often updating different fields at different times. This recipe shows how this can be achieved without having to create new schemas each time.

Getting ready

Open the job jo_cook_ch07_0080_fieldSelection.

How to do it…

The steps to be performed are as follows:

1. Open the tMysqlOutput component and change the field Action on data from Insert to Insert or update.

2. Now click on the Advanced settings tab.

3. Tick the box Use field options to reveal the Field Options panel. You should see that all the fields are set as Insertable and Updatable.

4. Uncheck createdDate and createdBy in the column Updatable.

5. Uncheck updatedDate and updatedBy in the column Insertable.

6. Finally check the Update Key column for the column id. Your Field options should now look as shown in the following screenshot:

   

7. Run the job and inspect the table. You will...