can you tell us why you're attempting an SQL join on ROWID? off the top of my head i can't think of it being useful for anything but creating a slightly inconsistent recordset, which would be great fun for irritating your people in the field, but i can't think of what else...
-----Original Message----- From: Robert Crossley [mailto:[EMAIL PROTECTED] Sent: Tuesday, 11 November 2003 7:49 PM To: [EMAIL PROTECTED] Cc: MapInfo List Subject: Re: MI-L MBX - Add Column statement - MI crashes in a heap! Hi, The trick I use is to make the selection before you add the column, thus it only works on the selected records. Make a selection using SQL of the joined tables and update that. ie. Select _RowID, MyID, DestinationField, SourceField From Table_A, Table_B Where Where _RowID = MyID Into UpdateList Add Column UpdateList(DestinationField) From UpdateList Set To SourceField R On Tue, 11 Nov 2003 17:57:34 +1100, Peter Zyczynski <[EMAIL PROTECTED]> wrote: > Alright, the problem is definitely with joining on RowID from Table_A. > > Got around this by creating a new column called _RowID in Table_A and > populated it with RowID, and join on that. > > BUT a new problem arises, it appears the where clause is ignored - All > rows > in Table_A get updated, rather than just the ones limited to the where > clause. Confused? I am. > > The code now looks like this: > '----------------------------------- > sub main > ' Create a temporary _RowID column in Table_A. > Alter Table "Table_A" ( add _RowID Integer ) > > ' Populate the new _RowID field. > Update Table_A Set _RowID = RowID > > ' Update Table_A with values from Table_B. > Add Column Table_A (DestinationField) > From Table_B > Set To (SourceField) > Where _RowID = MyID > ' The above statement updates all records in > ' Table_A, rather than just where they join to Table_B. > > ' Remove the temporary _RowID field. > Alter Table "Table_A" ( drop _RowID ) > end sub > '----------------------------------- > > I was hoping to avoid doing any select queries and just do the operation > in > one update query, got 200K+ records, also this operation will be run > regularly so the least number of select/update queries the better. > > Cheers, > Peter Zyczynski > > > -----Original Message----- > From: Peter Zyczynski [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 11 November 2003 2:52 PM > To: [EMAIL PROTECTED] > Subject: MI-L MBX - Add Column statement - MI crashes in a heap! > > Help! > > Trying to update "DestinationField" in "Table_A" with values from > "SourceField" in "Table_B", Mapinfo spits out the following error and > shuts > down: > > "The instruction at "0x00515248" referenced memory at "0x00080101". The > memory could not be "read". Click OK to terminate, CANCEL to debug." > > To keep things simple I whipped up this little sample: > > '----------------------------------- > sub main > > Add Column Table_A (DestinationField) > From Table_B > Set To (SourceField) > Where RowID = MyID > > end sub > '----------------------------------- > > I tried doing the above statement from within Mapinfo in the Mapbasic > window > but the effect is the same - CRASHOLA! > > I have a suspicion that its because I'm joining on RowID, ie: > Table_A.RowID = Table_B.MyID > > So, is it me, is it Mapinfo, or is it both of us? > > > Cheers, > > Peter Zyczynski > Analyst Programmer > Insight GIS > Australia > Ph: (03) 6234-5833 > Fax: (03) 6234-5899 > [EMAIL PROTECTED] > www.insightgis.com.au > > > > --------------------------------------------------------------------- > List hosting provided by Directions Magazine | www.directionsmag.com | > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > Message number: 9099 > > > --------------------------------------------------------------------- > List hosting provided by Directions Magazine | www.directionsmag.com | > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > Message number: 9103 > -- Robert Crossley Agtrix P/L 9 Short St New Brighton 2483 Far Southern Queensland AUSTRALIA 153.549004 E 28.517344 S P: 02 6680 1309 F: New Connection M: 0419 718 642 E: [EMAIL PROTECTED] W: www.agtrix.com W: www.wotzhere.com --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 9104 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 9113
