I don't think your query will have produced a result, or even run, in Manifold 9.
You "should" have seen the error message "Schema should contain at least one unique index".
Please let me know if that is not the case.
There are two reasons why the query cannot work in 9, AFAIK.
For a table to be updated, the SQL engine must be able to tell one record from another. It can only do this if the table has (as the error message says) contains a unique index on at least one field. That means a BTREE index.
Most often, for built-in tables, there is already a BTREE index on the special field mfd_id. In some cases that may not be true, depending on the source of the data, but there may be a BTREE index on some other field or on a combination of fields. That is equally good.
If there is no BTREE index, then we can neither UPDATE the table using SQL, nor even select any records in the table with the mouse. That is for the same reason: the SQL engine (which the table user interface also uses) has no means of telling one record from another.
(This is new, in the sense that Manifold 8 handles all this automatically. We didn't even need to know about it. Now we do.)
For an UPDATE query to work in 9, the target table must include not only the target field(s) and some source field(s) or source expression(s), but also some field on which a BTREE index exists.In simple cases, that is often as easy as including an existing mfd_id field in the list of fields.
But this is not a simple case, because of the join.
Take a look at your target table. To do this, highlight just the query text between the outer (...),
select ofs.[Geom] as oldGeom, newC.[Geom] as newGeom, ofs.[GEOID_Data]
from [AnotherDwg 2 Table Center, Inner] as newC
join [offShoreCentroids Table] as ofs on newc.[geoid_data] = ofs.[GEOID_Data]
and press Alt-Enter to execute just that portion of code.
Now press Ctrl-E to bring up the Schema for the resulting table.
(That's two great features, by the way. You may know about them already.)
What indexes do you see? (Indexes are at the bottom, after the list of fields.)
I would expect that you see RTREE indexes on [oldGeom] and [newGeom], and perhaps a BTREEDUP index on [GEOID_Data]. But crucially, no BTREE index at all. Is that right?
If there is no BTREE index, then the target table (created by the SELECT ... FROM ... [INNER] JOIN ...) cannot be updated.
This can be fixed, but first let's check whether that is indeed what you are seeing and if you have any questions so far.