Join Example: Update an Existing field with Join

In other examples using the Edit - Join dialog we have added new fields to the original table, which were then populated with data from the joined table.   We can also populate an existing field in the original table with data from the joined table.  This topic shows how.

 

To follow along with table examples in this topic, download from the Examples page on the Manifold website the books.mxb project.  That project contains the publishers and titles tables used in examples.

 

See the video version of this example in the Join Dialog Part 2 - Joins between Tables video.  

 

 

Suppose our publishers table already has a field called average pages.   We would like to populate that field with values from the titles table.

 

 

Each title in the titles table has a pages field that gives the number of pages in the title.  We will use that field.

 

With the focus on the publishers table, we choose Edit - Join from the main menu.

 

In the Join dialog we choose titles as the joined table, using pub_id as the key field.

 

 

We double-click into the joined table field cell for the average pages field row.

 

 

From the pull down menu showing fields in the titles table, we choose pages.     

 

The pull down menu will show all fields in the joined table that can be used with any transfer method.    If we had already double-clicked into the transfer method cell and had picked a transfer method, the pull down list of fields would only show us those fields from the titles table that can be used with that transfer method.

 

 

The pages field in the titles table is the same data type, int32 as the average pages field in the publishers table, so by default the transfer method is copy.  We want the average, so we double-click into the transfer method cell to choose a different transfer method.

 

 

We choose average.

 

 

Check the Save update query box (in case we want to run an update query later on) and then press Join.

 

 

The average pages field is populated with the average of pages across all titles for each publisher.  Publisher with ID P04 has no titles, so that value is NULL.

 

Notes

Terminology - The original table also may be called the target table, and the table providing data also may be called the source table.

 

Quick reference - With the Join dialog open, press F1 to open a quick reference guide in a browser window.

 

Guessing the key field - When we launch the dialog with an original field and when we choose a joined field, the dialog will try to guess what fields we might want to use as key fields and will load those first by default.  If it guesses wrong, we can choose the field we want.   For the original table, the dialog tries to use a field (other than mfd_id) that has a BTREE / BTREENULL index, with a data type preference for numbers, then text, then everything else, and a name preference for field names with id in them, such as ... id or ..._id, then ...id and then everything else. For the joined table, the dialog uses similar logic, but first tries to use a field with the same name as in the original table.

 

Added fields are static - Added fields are static, that is, if data in the joined table changes the fields in the original table that received data in the Join operation from the joined table will not automatically be updated.  We can easily update the table any time we want using a saved update query.  

 

Spatial joins are automatically parallel - Joining data between drawings uses parallel GeomOverlay function variants.  Joins from images to drawings are parallelized through a nested SELECT using THREADS.

 

Why only in the same data source - The Join dialog works only with tables and queries within the same data source to keep the dialog simple and fast.   An important part of keeping the dialog simple is to show only those tables or queries in the pull down list for the joined table that can be used in a join.   Scanning all tables and queries in the same data source is reasonable, but if there are many data sources or many levels of nesting (data sources within data sources) there could be hundreds of thousands, if not millions, of tables and queries within the hierarchy to scan.  For people working in map projects without such elaborate data sources, most of the time what they do will be within the .map project data source so everything works by default.  People who link in data sources can quickly add a simple aliasing query, as illustrated in this topic, to use tables and queries from other data sources.   This method also makes it possible for the same Join dialog to work within other data sources, when aliasing queries refer back either to the .map project data source or to other data sources.

 

Buy and read a Fehily book on SQL - Manifold recommends Chris Fehily's excellent books on SQL.  Chris makes learning SQL really easy.  To encourage Manifold readers to get a Fehily book and to read it, Manifold examples are often integrated with examples in the Fehily SQL books, such as the tables used in this topic.    

 

Example data - To follow along with table examples in this topic, download from the Examples page on the Manifold website the books.mxb project.  That project contains the publishers and titles tables used in examples.

 

Videos

Join Videos

 

Find Percentages of Open Space in ZIP Code Area - Find the percentage of open space in each ZIP code area given a layer of polygons representing ZIP codes and a layer of polygons showing open spaces like parks and green spaces. This video shows how to do that start to finish in a few simple steps, from initial importing of shape files to final results, in just five minutes, with an additional few minutes of explanation what each step does. Works in Manifold Release 9 or using the free Manifold Viewer.

See Also

Maps

 

Tables

 

Queries

 

Drawings

 

Images

 

Editing and Combining Data

 

Join

 

Join Videos

 

Join Examples

 

Command Window

 

JOIN Statements

 

Editable Results Tables