Filling the New Column with Values from Another Table - MapBasic - 2023

If you specify a From clause instead of a Values clause, MapBasic derives the values for the new column from a separate table (source_table). Both tables must already be open.

When you use a From clause, MapInfo Pro joins the two tables. To specify how the two tables are joined, include the optional Where clause. If you omit the Where clause, MapInfo Pro automatically tries to join the two tables using the most suitable method.

A Where clause of the form Where column = column joins the two tables by matching column values from the two tables. This method is appropriate if a column from one of your tables has values matching a column from the other table (e.g., you are adding a column to the States table, and your other table also has a column containing state names).

If both tables contain map objects, the Where clause can specify a geographic join. For example, if you specify the clause Where Contains, MapInfo Pro constructs a join by testing whether objects from the source_table contain objects from the table that is being modified.

The following example adds a "County" column to a "Stores" table. The new column will contain county names, which are extracted from a separate table of county regions:

Add Column 
	stores(county char(20) 		'add "county" column 
	From counties 		'derive data from counties table...
	Set to cname 		'using the counties table's "cname" column
	Where Contains 		'join: where a county contains a store site

The Where Contains method is appropriate when you add a column to a table of point objects, and the secondary table represents objects that contain the points.

The following example adds a temporary column to the States table. The new column values are derived from a second table (City_1K, a table of major U.S. cities). After the completion of the Add Column statement, each row in the States table will contain a count of how many major cities are in that state.

Open Table "states" Interactive 
Open Table "city_1k" Interactive 

Add Column states(num_cities) 
	From city_1k 		'derive values from other table
	Set To Count(*)		'count cities in each state
	Where Within 		'join: where cities fall within state

The Set To clause in this example specifies an aggregate function, Count(*). Aggregate functions are described below.