Copying objects to existing records in MapInfo

Question

This should be fairly simple but I think I'm missing something...

I have two tables of similar structure with corresponding data in them (i.e. same number of rows, rows have the same IDs).

The main difference is that one table has objects attached, the other doesn't.

I would like to copy the objects from one table to the other. I expected the following query to work but I get an error...

UPDATE Tab1 
SET Tab1.Obj = Tab2.Obj 
WHERE Tab1.Site_ref = Tab2.Site_ref

Error: Expression does not evaluate to a column or table name.

Any ideas?

Answer 1

An update statement can only refer to a single table at a time.

You could do it this way with a function instead:

UPDATE Tab1 SET Obj = myobj(Site_ref)

declare function myobj(id as integer) as obj
  select * from tab2 where site_ref=id into tmp1 noselection
  myobj=tmp1.obj
end function

Untested !

Answer 2

How about running an SQL join:

select tab1.attr1, tab1.attr2
from tab2, tab1
where Tab1.Site_ref = Tab2.Site_ref
into q_ToSave

commit table q_ToSave as "C:\NewTable.tab"

This works.

If Tab1 doesn't have objects in it then the order of the tables in the from clause is not important, but if there are objects in tab1 to be replaced, then specifying tab2 first in the list will ensure that the tab2 objects are saved in NewTable

An alternative using undocumented behaviour (including a second object column in a query) is to do the following. This requires that Tab1 is already mappable, and Tab1 must be first in the from clause list:

select tab2.obj "Tab2Obj"
from tab1, tab2
where Tab1.Site_ref = Tab2.Site_ref
into q_ToSave

update q_ToSave set obj = Tab2Obj

This should directly overwrite the objects in Tab1 where the join is true.

Answer 3

There is a MapBasic program called ReplaceObjects available for download here.

I'd be interested in a one-line sql-like answer but it seems that may not be possible.