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: 16674
