Hi Robert, First of all, an update is to be performed on one table, not two as in your sample queries.
Secondly, you're missing a vital part of the statement, namely the join clause. In contrast to the somewhat special/limited SQL syntax in MIPro, all statements in MS/SQL involving two or more tables need an explicit join clause. Typically an inner join or a left/right join, depending on your requirements. It's also possibly to define an outer/cross join if nothing alphanumeric binds the tables together, e.g. if it's a spatial match. Think of MIPro as always performing an implicit outer/cross join. Take a look at the sample in "Using Cross Joins" in MS/SQL Books Online. And lastly, I think all statements with spatial condition needs to be run thru "sp_spatial_query". But I'm not 100% sure whether this applies to all kinds of statements (select, insert, update) or just some of them. Better to always use it. Best regards/Med venlig hilsen Lars V. Nielsen GisPro, Denmark http://www.gispro.dk/ ----- Original Message ----- From: "Robert Crossley" <[EMAIL PROTECTED]> To: "MapInfo List" <[email protected]> Sent: Sunday, June 05, 2005 5:57 AM Subject: MI-L spatailware update query Hi all, Thanks for the answers to the queries on spatialware so far, particularly lars and anand. I will post a summary of the strategy used and the tricks learned once I get a bit more understanding of what I have actually done. I can join 2 tables with a spatial query as follows: exec sp_spatial_query ' select a.C_LINKCODE, b.I_GPSLOCS_ID from T_AMAL_HARV_OPS a, T_GPS_LOCS b where ST_Contains(a.sw_geometry, b.sw_geometry)' My latest problem is to try to update a column with the value from field from another table, but where the join is by a spatial relationship. Logically, it should be something like: Update T_GPS_LOCS , T_AMAL_HARV_OPS SET T_GPS_LOCS.C_PAD_ID = T_AMAL_HARV_OPS.C_LINKCODE Where dbo.ST_Contains(T_GPS_LOCS.SW_GEOMETRY, T_AMAL_HARV_OPS.SW_GEOMETRY) but have been trying: exec sp_spatial_query ' Update T_GPS_LOCS , T_AMAL_HARV_OPS SET T_GPS_LOCS.C_PAD_ID = T_AMAL_HARV_OPS.C_LINKCODE Where dbo.ST_Contains(T_AMAL_HARV_OPS.SW_GEOMETRY, T_GPS_LOCS.SW_GEOMETRY)' OR exec sp_sqlexec ' Update T_GPS_LOCS , T_AMAL_HARV_OPS SET T_GPS_LOCS.C_PAD_ID = T_AMAL_HARV_OPS.C_LINKCODE Where dbo.ST_Contains(T_AMAL_HARV_OPS.SW_GEOMETRY, T_GPS_LOCS.SW_GEOMETRY)' OR Update T_GPS_LOCS , T_AMAL_HARV_OPS SET T_GPS_LOCS.C_PAD_ID = T_AMAL_HARV_OPS.C_LINKCODE Where 'ST_Contains('+CAST(T_AMAL_HARV_OPS.SW_GEOMETRY AS varchar(25))+', '+CAST(T_GPS_LOCS.SW_GEOMETRY AS varchar(25))+ ')' Any leads? r -- ________________________________________________ Robert Crossley Agtrix P/L 9 Short St PO Box 63 New Brighton 2483 Far Southern Queensland AUSTRALIA 153.549004 E 28.517344 S P: 02 6680 1309 F: 02 6680 5214 M: 0419 718 642 E: [EMAIL PROTECTED] W: www.agtrix.com W: www.wotzhere.com skype: robertcrossley --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 16671 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 16672
