Hi Robert, The join clause needs to be set in MS/SQL typically as one of the two samples below :
-- with an alphanumeric join clause Update Table1 Set Field1 = Table2.Field2 From Table1 inner join Table2 on Table1.id = Table2.id Where .... -- without an alphanumeric join clause Update Table1 Set Field1 = Table2.Field2 From Table1 cross join Table2 Where .... Best regards/Med venlig hilsen Lars V. Nielsen GisPro, Denmark http://www.gispro.dk/ ----- Original Message ----- From: "Robert Crossley" <[EMAIL PROTECTED]> To: "Lars V. Nielsen (GisPro)" <[EMAIL PROTECTED]> Cc: "MapInfo List" <[email protected]> Sent: Monday, June 06, 2005 6:38 AM Subject: Re: MI-L spatailware update query Thanks Lars, Yes it seems that I was using a sql format that was probably from MS Access, and I did find the correct syntax later in MSDN (I normally use google first). The normal format is as follows: Update Table1 Set Field1 = Table2.Field2 From table1,Table2 Where .... Or you can have an inner join specified in the From clause. I couldn't get this to work using a spatial join so have created a stored procedure that uses a spatial query to get the result and a key to the table to be updated, inserts this data into a temproary table, and then updates the original table from a normal update query. This works quite well. The script is as follows (I may get around to parameterising it at some point in the future if I need to use it more.) CREATE TABLE #Data (C_LINKCODE varchar(25), I_GPSLOCS_ID int) INSERT INTO #Data 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)' UPDATE dbo.TMP_GPS_LOCS SET dbo.TMP_GPS_LOCS.C_PAD_ID = D.C_LINKCODE FROM #Data AS D WHERE dbo.TMP_GPS_LOCS.I_GPSLOCS_ID = D.I_GPSLOCS_ID drop table #Data On Sun, 5 Jun 2005 13:02:50 +0200, Lars V. Nielsen (GisPro) <[EMAIL PROTECTED]> wrote: > 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: 16675
