MapInfo Pro

 View Only
Back to discussions
Expand all | Collapse all

MapInfo Monday: Update Table with Values from Another Table

  • 1.  MapInfo Monday: Update Table with Values from Another Table

    Employee
    Posted 07-14-2025 03:27

    A very common task in MapInfo Pro is to update one table with values from another table. You may want to update all the postal sectors with the total sales from the stores in these postal sectors. Or you want to count the number of pupils in each of your school districts or the number of registered votes in each voting district.

    The use cases are endless.

    The Update Column dialog in MapInfo Pro can help you transfer values or aggregates from one table to another.

    Let me give you a quick tour.

    Happy #MapInfoMonday!

    The Update Column dialog

    You can find the Update Column control from the SQL dropdown on the Table, Map, and Spatial tabs. On the Table tab, the Update Column control can also be found directly in the Edit group.

    Click the control to open the Update Column dialog.
    Start by selecting the table to update from the Table to Update list.
    Now select the column to update via the Column to Update list. You can choose to add a temporary column and update this column with the values. 
    This is useful if you don't need to keep the values as static values in the table. It is also useful if you want to update the column with fresh values as they change in the other table. The updating of a temporary column will be saved as a statement to a workspace.
    Or you can select an existing column in the table.
    Next, you will have to choose the table to get the values from using the Get Value from Table list. By default, this is set to the same as the table to update. In my example, I'm changing this to the Stores table.
    Click on the Join... button to configure the join condition between the two tables.
    In the Specify Join dialog, you can configure how you want to join the two tables.
    The join can be a column-based join where values in a column in the first table need to match a value in a column in the second table.
    As you can see above I have set the table to be joined on the column POSTCODE from both tables. The column doesn't have to have the same name in the two tables.
    You can also specify a Spatial Join. Below you can see the options from the list of spatial join conditions.
    I picked the Is within option. 
    Make sure that you use the correct spatial join condition. Contains and Is Within depend on the table order.
    Back in the Update Column dialog, you can now configure what value to get from the other table.
    You can update the first table with values from a specific column in the other table. Start by selecting Value from the Calculate: list and then choose the column to read the values from.
    Note that you aren't limited to reading from a single fixed column. You can also update a column with an expression, allowing you to concatenate multiple columns or perform modifications to the value you are writing back, such as rounding or formatting.
    If the table you are updating can hold multiple records from the other table, you may want to consider using an aggregate like Sum.
     
    This allows you to get the total sum of values from all stores within each postal sector, for example.
    You can also use Count to count the number of records from the other table.
    MapInfo Pro supports these aggregates:
    • Average(expression): Calculates the average of the values in the expression for all the records in a group.
    • Count: Counts the number of records in a group. Takes * as its argument because it applies to the record as a whole and not to any particular field in a record.
    • Min(expression): Finds the lowest value in the expression for all records in a group.
    • Max(expression): Finds the highest value in the expression for all records in a group.
    • Sum(expression): Calculates the sum of the values in expression for all the records in a group.
    • WtAvg: With weighted averaging, MapInfo Pro adjusts the calculation of averages so that the values from each selected object are weighted more or less heavily.
    • Proportion Sum: Aggregates data into a polygon. Accounts for the area of the polygon that overlaps the polygon receiving the aggregation. For example, if a third of an object's area falls within a polygon, the proportion sum aggregate will put one-third of the overlapping object's data value in the polygon.
    • Proportion Avg: Computes the average based on the proportion of values from the covered areas (weighs the averages according to area). For example, if 80% of a new object's area is from Object A having a mortality rate of .8% and 20% of the new object's area is from Object B having a birth mortality rate of .65%, then the birth rate of the object would be equal to .8 x .008 + .0065 x .2 = .0077 or .77%
    • Proportion WtAvg: Computes the average based on the proportion of values from the covered areas (weighs the averages according to another field whose value is proportioned). For example, (continuing from the example above), the Population of the new object, Object A = 34,000. Population of new object, Object B = 26,000. The birth mortality rate is (.8 x 34,000 + .6 x 26,000) /6,000 = .713.

    The description above is from the Precisely Help Center. The last three aggregates only work if both tables are polygon-based.

    As you can see, you have a long range of options in the Update Column dialog when you are looking to get values from one table to another.

    I hope you found this useful. You may also want to read this article that shows you another way to update a table with values from another table: MapInfo Monday: Two Ways to Update a Table with Values from another Table



    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------