Update column with join based on 2 columns

[Tom Bacon]

Hi All,

 

This is probably fairly simple, but I can’t seem to work it out.  I would like to use an Update Column to update a column in one table based on a join with another table.  The problem arises in that I want to do the join based on 2 columns rather than one and MapInfo’s update column dialog does not allow for this.  Does anyone know if this is possible using an ‘And’ statement in MapBasic (or any other method)?  Any help would be appreciated.

 

Many thanks,

 

Tom

`

 

Tom Bacon - GIS/LLPG Assistant | Technical Services, Worthing Borough Council Location: Portland House, Richmond Road, Worthing BN11 1HS Internal: 1390 | External: 01903 221390 | E-mail: tom....@worthing.gov.uk

 

Please think before you print this and save paper.

 

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Important Information and Disclaimer.  Please do not print this e-mail unless necessary.The domain from which this e-mail is sent is not indicative of the  Council on whose behalf it is sent. The author will indicate in the body of  the e-mail on whose behalf it is sent. The information contained in this email may be subject to public disclosure  under the Freedom of Information Act 2000. Unless the information contained  in this email is legally exempt from disclosure, we cannot guarantee that we  will not provide the whole or part of this email to a third party making a  request for information about the subject matter of this email. This e-mail is intended exclusively for the addressee and may contain  information that is confidential and/or privileged. If you are not the  intended recipient please note that any form of distribution, copying or use  of this communication or the information in it is strictly prohibited and may  be unlawful. If you have received this communication in error please return it to the sender and delete the material from any computer and destroy any printed  copy. Adur District Council and Worthing Borough Council cannot guarantee that  this message or any attachment is virus-free or has not been intercepted or changed. The views of the author may not necessarily reflect those of either  Adur District Council or Worthing Borough Council. Any opinions or other information in this message that do not relate to the official business of Adur District Council  and / or Worthing Borough Council are neither given nor endorsed by them.

[Peter Horsbøll Møller]

Tom,

 

I would assume that you could create the join of the two tables thru SQL Select and then update the query afterwards.

 

Select * From TABLE1, TABLE2

Where TABLE1.COLUMN1 = TABLE2.COLUMN1

And TABLE1.COLUMN2 = TABLE2.COLUMN2
Into QUERY_TO_UPDATE

 

Update QUERY_TO_UPDATE Set COLUMNFROMTABLE1 = COLUMNFROMTABLE2

 

 

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

 

2009/9/9 Tom Bacon <Tom....@worthing.gov.uk>

[Tom Bacon]

Hi Peter,

 

Thanks for the reply; I did get there in the end.  Think I just got my head in a muddle, one of those days…

 

Tom

 

---

[Spencer Simpson]

This falls apart when the two columns have the same name, which is the case more often than you might think.

 

The solution is to be more...selective in the Select statement's column list, meaning you can use generic column names in the Update statement.

 

Select desttab.zipcode, srctab.zipcode from desttab,srctab where desttab.obj within srctab.obj into XFERTAB noselect

Update XFERTAB set COL1=COL2

Close table XFERTAB

 

 

---

Spencer

---

From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of Peter Horsbøll Møller

Sent: Wednesday, September 09, 2009 7:15 AM
To: mapi...@googlegroups.com

[Peter Horsbøll Møller]

Spenser

You are absolutely right.

You can also choose to "rename" the columns when selecting them:

 

Select desttab.zipcode "TO_UPDATE", srctab.zipcode "TO_READ" from desttab,srctab

where desttab.obj within srctab.obj

into XFERTAB noselect

Update XFERTAB set COL1=COL2

But in general it's a good idea to only select the columns you need.

 

 

Peter Horsbøll Møller
Pitney Bowes Business Insight - MapInfo

2009/9/9 Spencer Simpson <ssim...@baltometro.org>

[Spencer Simpson]

I've had bad experiences renaming columns (through MIPro 9.5); the names go away when you save the table, or don't work when you try to use them in aliases or ColumnInfo, especially if you've given a name to an expression in a Select statement.  But generic names always work.