update multiple columns

[Richard Greenwood]

I would like to update more than one column with a single SQL
statement but it appears that MapInfo only allows updating one column
in a SQL statement. Am I correct about this? Can anyone suggest a work
around?

Thanks,
Rich

--
Richard Greenwood
richard....@gmail.com
www.greenwoodmap.com

[Peter Horsbøll Møller]

Rich,

Luckily you are mistaken ;-)

Here is the syntax of the Update statement:
Syntax
Update table Set column = expr [, column = expr, ...]
        [ Where RowID = idnum ]

Translated to english this means that you can add extra columns simply by dividing the columns with a comma.

Here is an example:

Update MYTABLE
        Set ID = ROWID,
                X = CentroidX(OBJ),
                Y = CentroidY(OBJ)

HTH,

Peter Horsbøll Møller
GIS Developer, MTM GeoInformatics
Geographical Information & IT

COWI A/S
Odensevej 95
DK-5260 Odense S.
Denmark

Tel     +45 6311 4900
Direct  +45 6311 4908
Mob     +45 5156 1045
Fax     +45 6311 4949
E-mail  p...@cowi.dk
http://www.cowi.dk/gis

[Richard Greenwood]

Thanks. I should have know that!

Rich

[Richard Greenwood]

Some time ago I asked about updating more than one column with a
single SQL statement and Peter Horsbøll Møller provided an example
which updates multiple columns. I now need to update values in
multiple columns from data in a joined table. The standard SQL
statement would look like:
UPDATE table1 SET
columnA=table2.columnA,
columnB=table2.columnB
FROM table2
WHERE table1.id=table2.id;
But this syntax fails in MapInfo.

The menu option to update a column produces a statement like:
Add Column "Table1" (columnA) From table2
Set To columnA Where COL1 = COL1
But I can not expand on this to update multiple columns in a single
statement. Any suggestions?

--
Richard Greenwood
richard....@gmail.com
www.greenwoodmap.com

=============================

[Peter Horsbøll Møller]

Richard,

 

To steps is the best I can come up with...

'**Create the the join:

'** - select the columns to update

'** - and the columns to read the values from

Select TABLE1.COLUMN1 "1_A", TABLE1.COLUMN2 "1_B", TABLE1.COLUMN3 "1_C"

     , TABLE2.COLUMN "2_A", TABLE2.COLUMN "2_C", TABLE2.COLUMN "2_C"

From TABLE1, TABLE2

Where TABLE1.ID = TABLE2.ID

Into TO__UPDATE NoSelect

 

'**Update the columns

Update TO__UPDATE

   1_A = 2_A,

   1_B = 2_B,

   1_C = 2_C

 

In the example above I update 3 columns from TABLE1 with data from TABLE2.

 

Peter Horsbøll Møller

2008/12/14 Richard Greenwood <richard....@gmail.com>

[Spencer Simpson]

This is the only way to do it:

Select table1.columnA, table1.columnB,
table2.columnA "ColumnA2", table2.columnB "ColumnB2"
from table1, table2
where table1.id=table2.id
Into myjoin noselect
Set table table1 fastedit on
Update myjoin set columnA=columnA2, columnB=columnB2
Close table myjoin
Set table table1 fastedit off

You only have to do the column rename if both tables have columns involved
in the transfer with the same name. The fastedit stuff is optional, but
probably desirable.

Standard SQL DML would be a nice feature addition to MapInfo.

Hope this helps
________________________________

Spencer

[Richard Greenwood]

Thank you Peter and Spencer,

I had tried joining the two tables and then updating the query, but it
had failed for me because the source table was readonly (it was a
delimited text file). But your suggestions encouraged me to give it
another try, so thank you both.

Regards,
Rich

[Divya Sakpal]

Hi Peter,

I have four tables:

1. Polling_Booth (Point)

2. City (Polygon with column City_ID)

3. Town (Polygon with column Town_ID)

4. Village (Polygon with column Village_ID)

I want to update column Polygon_ID in Polling_Booth table with ID values from City, Town and Village tables.

My City, Town and Village polygons DO NOT overlap each other. Therefore, a polling booth will fall in either City, Town or Village.

So what I want to do is update Polygon_ID column in Polling_Booth table with IDs from City table, then update the same column with IDs from Town table where Polygon_ID=NULL and finally with IDs from Village table where Polygon_ID=NULL.

A simpler way of doing this would be to create three columns (City_ID, Town_ID, Village_ID) in Polling_Booth table and update each of them one by one using Update Column function.

But I want to do it in single query and update just one column instead of three.

[Uffe Kousgaard]

Hi,

If a single update query is a strict requirement, you will have to append the 3 tables first as a single table with a single ID field.

But all in all, that is more steps than doing 3 update queries.

Regards
Uffe Kousgaard

Divya Sakpal wrote:

--
--
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.

[Divya Sakpal]

Hi Uffe Kousgaard,

Exactly. Doing 3 update queries is preferred over appending the tables.

I was just looking for a quicker solution, as it would be useful to me in the future.

Thanks!

Regards,

Divya Sakpal