MapBasic - Updating a table field from another table

[Tom]

Can anyone help?

I have two identically formatted tables. ILEC_ADDR.tab and
Current_Trans.tab

I first manually select a row in ILEC_ADDR. I want to update the
Phone_Nbr field in that table with the Contents of the Phone_Nbr field
in the Current_Trans.tab.

I then try to run the following MapBasic program.

Include "MapBasic.def"
Include "Menu.def"

Define Phone Current_Trans.Phone_nbr
Fetch first From Current_Trans

Update Selection
Set Phone_Nbr = Phone

I get the following error:

"Variable or Field Current_Trans.Phone_Nbr not defined"

Where am I going wrong?

[Bill Thoen]

You've got several problems here. First, you declare variables that get
translated to the contents of a database field as 'alias' type. You
don't define them as a constants. Also a 'fetch' does not create a
selection, so you can't use "update selection" as you are. Third, you
aren't specifying how the two tables are supposed to be matched
together, so at best (or worst, as the case may be) you're setting up to
randomly update phone numbers.

If there is a key field that relates these two tables together (like
Cust_Id in this example) , then use that with an SQL select and update
like this:
SELECT A.Phone_Nbr, B.Phone_Nbr "Nbr" FROM ILEC_ADDR A, Current_Trans B
WHERE A.Cust_Id = B.Cust_Id INTO tmp
UPDATE tmp SET Phone_Nbr = Nbr
COMMIT TABLE ILEC_ADDR
CLOSE TABLE tmp

You can put these commands right in your MapBasic program. What's going
on here is we assign short alias names ( A and B) to the two tables so
we can differentiate the field names that are spelled the same. We then
join the tables extracting the Phone_Nbr fields into a temporary table
named tmp. Then we update tmp setting the first column equal to the
values in the second column. Because these fields are in a temporary
table they are really still attached to their actual tables. Finally,
the COMMIT saves the changes and we close the tmp table.

If you don't have a key field to join these tables, then you really
ought to make one.

[TOM halbrook]

Thanks Bill;

Your suggestion makes sense to me after I thought about it more.

Thanks Again

Tom

> Define Phone Current_Trans.Phone_nbrhas> Fetch first From

[Logendra Elayathamby]

Hi Bill,

Could you please send me the mapbasic code to copy a row data from a table to another table.

Thank you,

Logendra

[Bill Thoen]

Sorry, but I don't have any code to do just that. If the SQL code included in your email doesn't help, then what have you tried so far? 

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Logendra Elayathamby]

Hi Bill, I've tried the update column in MapInfo Pro, but it update the column and put zeros in the rest of the column as the original table has only one row data. Is it possible to update a cell from one table to another?

Thank you,
Logendra

[Bill Thoen]

Yes, you can update a single cell/field, but you have to make sure your tables are joined properly. It sounds like what's happened is that you've appended a row, and not just updated a column. Can you give me some more information about what you're trying to do? Specifically, the SQL statement you're trying to use? Table structures?

[Logendra Elayathamby]

Hi Bill,

Thanks for your response, as per the screenshot attached i've tried to update the Period value of Area 9 in areas table from table _9_1. Once i clicked the OK button in the Update Column window, it updates the column as 4 for Area 9 and the remaining column values to zero. Please advise.

Thank you,

Logendra

[Bill Thoen]

There are 2 things going on here. First, it's updating all rows with area, not period. If you open your MapBasic window (under options/show MapBasic window) and run the query you'll see the SQL. Change 'set to area' to 'set to period' to fix that. But the big problem is that it updates all the rows. And where it doesn't have a match, it will use a zero.

I don't have the latest Mapinfo, so I don't know if it has a real SQL engine yet, but you'll probably have to do this in a several steps. The SQL would look something like this:

Close all
Open table "areas.tab" as a interactive
Open table "_9_1.tab" as b interactive
Select a.area, a.expenditure, b.period from a,b where a.area=b.area into tmp
Insert into a(COL1, COL2, COL3)
Select COL1, COL2, COL3 from tmp
Select * from a where period = 0 into tmp2
Delete from tmp2

Basically what I'm doing here is updating a column by adding a complete new row and then deleting the old row. The a and b table names are just alias names to avoid typing more than I need to. Just copy the above code into the MapBasic window, select it and press Enter. It does some deleting, but it won't save changes. You can add a commit statement if you like. There are other ways to do this but since I couldn't tell if you were using the UI or MapBasic, this should work with both.

Btw, does anyone know if MapInfo has upgraded their SQL engine yet? This type of query ought to be MUCH simpler.

> On Nov 6, 2015, at 9:15 PM, Logendra Elayathamby <loge...@gmail.com> wrote:
>
> Hi Bill,

[Peter Horsbøll Møller]

Bill is right.

 

The problem you are seeing is caused by you trying to update a column in one table with data from another table. And where there's no match, the value will be set to zero.

 

MapInfo Pro has worked like that for decades and whether that's right or not is a discussion we always can take :-)

 

There's however a more simple solution:

 

1. Join your two tables using SQL Select and only select the columns you really need for your update (just to make it easier):

Select areas.area "areas_area", areas.period "areas_period", _9_1.period "_9_1_period"

From areas, _9_1

Where areas.area = _9_1.area

Now you have a query where the rows have been linked where the areas are the same.

Next step is to update the column period from the table areas with the data from the table _9_1.

 

2. You can do this using the Update Column where you pick the query you got from the SQL Select above:

Update Query1

Set areas_period = "_9_1_period

 

Because you are updating a query, you'll see that the values written to this is stored in the base table the query is based upon.

 

Peter Horsbøll Møller

GIS/LI Presales Specialist

Location Intelligence | MapInfo

 

M: +45 29 133 769

peter....@pb.com | @phorsbollmoller

pitneybowes.com/dk | mapinfo.com

 

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of Bill Thoen

--

--

You received this message because you are subscribed to the Google Groups "MapInfo-L" group.To post a message to this group, send email to mapi...@googlegroups.com To unsubscribe from this group, go to:

http://groups.google.com/group/mapinfo-l/subscribe?hl=en

For more options, information and links to MapInfo resources (searching archives, feature requests, to visit our Wiki, visit the Welcome page at http://groups.google.com/group/mapinfo-l?hl=en

 

---

You received this message because you are subscribed to the Google Groups "MapInfo-L" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Logendra Elayathamby]

Thank you Peter for your explanation and support. Yes its working now.

Thanks again.

Logendra

To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Logendra Elayathamby]

Hi Peter/Bill,

I wan to update entire row by joining the tables manager and client. I have executed the following mapbasic commands and it throws an error message as mentioned below, Please can you look on it and advise me to correct it.

MapBasic command: 

Select manager.area "manager_area", manager.expense "manager_expense", client.expense "client_expense" From manager, client Where manager.area = client.area into Selection

Update manager Set manager_expense = "client_expense" from Selection

Error:

Field manager_expense does not exist in table manager.

Many thanks,

Logendra

[Peter Horsbøll Møller]

Hi

 

Change this line:

Update manager Set manager_expense = "client_expense" from Selection

 

To:

Update Selection Set manager_expense = client_expense

 

Peter Horsbøll Møller

GIS/LI Presales Specialist

Location Intelligence | MapInfo

 

M: +45 29 133 769

peter....@pb.com | @phorsbollmoller

pitneybowes.com/dk | mapinfo.com

 

To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

 

---

 

--

--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to

mapi...@googlegroups.com

To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Logendra Elayathamby]

Hi Peter,

Many thanks for you quick response, the code updates the entire column, but actually i wanted to update a specific row data set by an area = "11". I want to accomplish to update the manager table from the client, by matching the area code. Hope my requirement is clear to you!.

Thank you,

Logendra

[Logendra Elayathamby]

Hi Peter,

Many thanks for you prompt response. Is there any way to access google maps in MapInfo Pro version 12.5. I've tried MapperG, which provide only 3 day free access, quite good one. Any addons for free, or increased trial period for at least a month.

Thank you,

Logendra

[Sancarn]

If you're looking for something free, using something akin to google maps, then you will probably have to programme it yourself using the GoogleMaps API. At least that will be free up to 2-3k uses per day which is more than enough for individuals using it. However if this is something for a company you are with and it is a tool that many people are likely to use then you are looking at something that likely isn't going to be free. GoogleMaps API is still affordable though if you want the cheaper option though it'll require more effort on your part, unless someone comes up with some open source software at some point.

You can use GoogleEarth with GELink - if you have a Google Earth License. This is of course a no-hassle approach but requires money like many other things in this world. I would also note that GELink is a 1-way system. You can export mapping objects to google earth with GELink but you cannot modify them in GoogleEarth itself. If you want to modify the data you need to go back to MapInfo to change it there.

[Ben]

 How can I copy entire data from one column and paste in another column in the same table in MapInfo Professional? Any idea how can I do that?

[Peter Horsbøll Møller]

You can use Update column.

 

Table to Update: Pick the table

Column to Update: Pick the column you want the values to go into

Get Value from: leave it as the same table as the table to update

Value: write the name of the column to read the values from – or select it using the Assist button

 

Peter Horsbøll Møller

EMEA Channel Enablement Specialist

Location Intelligence | MapInfo

 

M: +45 29 133 769

peter....@pb.com | @phorsbollmoller

pitneybowes.com/dk | mapinfo.com

 

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alexander John Viray]

Hello there can I ask for a little favor, could you please send me some of your pdf tutorials regarding on mapbasic programming for beginners, thanks.

[RM]

Hi

Can anybody help me understand how to fix the issue with the code below. It won't compile but I'm not sure how to go about correcting it. 

I need an if statement that says if the system field in table 1 matches the system field in table 2 (they are named slightly differently within the tables) then update table 2 with any area from Table 1 that is within Table 2...
 

Im trying to update subcatchments with survey areas Thanks in advance!

If Subcs.system_type = Survey.System Then

Add Column Subcs (area_1 )From Survey Set To Total_Area Where within

Else

End If

Any help is much appreciated! Thanks 

[Peter Horsbøll Møller]

I can compile this:

 

If Subcs.system_type = Survey.System Then

   Add Column Subcs (area_1)

      From Survey

      Set To Total_Area

      Where within

Else

 

End If

 

Which must mean that you might have a variable with the same name as a table or column mentioned above.

I’m assuming that the values marked in red all are hard coded table or column names. Is that correct?

 

Peter Horsbøll Møller

Pitney Bowes

 

From: 'RM' via MapInfo-L [mailto:mapi...@googlegroups.com]

Sent: 4. juli 2017 18:13
To: MapInfo-L <mapi...@googlegroups.com>

[RM]

The survey Table is a selection that a commit to a table and reopen before this part of the code, but the subc's table is whatever table the user picks from a drop down which have to have system_type in. Is that the problem? 

Is there any way I can keep the 'select the subcatchments table' from the drop down and be able to use this kind of if statement?

Thank you very much for your response!

[Cliff B]

If you're storing a tablename in a variable you'll need to use an alias to reference a field in that table.

So you'd need

Dim Subcs_system_type as alias

Subcs_system_type = Subcs + ".system_type"

If Subcs_system_type = Survey.System Then