How Do I Update a Table Column with a Value from Other Columns in the Table? (Magic xpi 4.6)

The Magic xpi Data Mapper utility lets you update fields in database tables. To use fields from the same table as part of the Update expression, you define the same table as both Source and Destination in the Data Mapper. In this case, use the Update operation for the Destination and the Select operation for the Source. Map the required fields from the Source to the Destination.

Another way to update a column in a table with the value of another column(s) in the same table is by changing the SQL statement in the last screen of the database wizard (click here for more information).

Example

In this example Col1 updated with the sum of Col1 and Col2:

Defining the Source

1. Open an existing flow or create a new flow in Magic xpi.

2. Drag the Data Mapper utility to your flow.

3. Double-click or right-click on the Data Mapper and click Configuration to open the Data Mapper window.

4. From the Toolbox's Mapper Schemas section, drag a Database type into the Source area of the Data Mapper window.

5. In the Database Definition drop-down list, select your database.

6. Click Wizard to open the Database Wizard.

7. In the Select Tables screen, select Example_Table from the Available tables list and click Add to add it to the Selected table list. Click Next to open the Select the Columns screen.

8. Select Col1 from the Available columns list and click Add to add it to the Selected columns list. Click Next to open the Where Clause screen.

9. Click Next to open the Wizard Result screen and then click Finish to close the wizard and return to the Data Mapper window.

Defining the Destination

1. From the Toolbox's Mapper Schemas section, drag a Database type into the Destination area of the Data Mapper window.

2. Click Wizard to open the Database Wizard.

3. In the DB Operation list, select Update.

4. In the Select Tables screen, select Example_Table from the Available tables list and click Add to add it to the Selected table list. Click Next to open the Where Clause screen.

5. Click Next to go to the Wizard Result screen and then click Finish to close the wizard and return to the Data Mapper window.

6. In the Data Mapper window, map Col1 and Col2 from the Source to Col1 in the Destination.

7. Right-click Col1 in the Destination list.

8. In the Properties pane, click to the right of the Calculated Value field to open the Expression Editor.

9. Create an expression by doing the following:

1. Select Col1 from the Variables list.

2. Select the + operator from the Operators list.

3. Select Col2 from the Variables list.

The expression that you created appears in the Expression window at the bottom of the Expression Editor. It should look like this:

Col1+Col2

This process will update Col1 with the value of Col1+Col2.