Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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 !

share|improve this answer
    
This works perfectly unless there is a record in tab2 which doesn't have an object attached. –  Mr_Chimp Apr 11 '12 at 15:59
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.