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

The Magic xpi Data Mapper Service 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 Service to your flow.

3. Click Configuration to open the Source/Destination Management dialog box.

4. In the Source area, create a new Database entry and click Properties to open the Database Schema Properties dialog box.

5. From the DB Operation list, select Select and from the Database Definition drop-down list.

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 Source/Destination Management dialog box.

Defining the Destination

1. In the Destination area, create a new Database type entry and click Properties to open the Database Schema Properties dialog box.

2. From the DB Operation drop-down list, select Update.

3. Click Wizard to open the Database Wizard.

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 Source/Destination Management dialog box.

6. In the Source/Destination Management dialog box, click Map to open the Data Mapper screen.

7. Map Col1 and Col2 from the Source to Col1 in the Destination.

8. Right-click Col1 in the Destination list and select Properties from the shortcut menu. The Node Properties dialog box opens.

9. Click to the right of the Calculated Value field to open the Expression Editor.

10. 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.