UK Users Group

Hi,

I have a table with a blank column I would like to fill based on values in other columns from the same table.

For example 
If Column A, B and C contain the value '1', I would like to update Column D (empty column) with the value of '1'.
I have written this in the Update Column expression box as:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=1) = 1
Result: Columns D = 1 (where other column value criteria is met)
This seems to populate the correct '1' value in the correct rows. 

However, when I alter this slightly to populate the value '2' mapinfo populates the value of '1' again.
For this expression I wrote:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=-1) = 2
Result: Column D = 1 (where other column value criteria is met)
Desired result: Column D = 2 (where other column value criteria is met)
For the second expression I would like Column D to have a value of 2 where those criteria are met. I don't understand why it is populating a value of 1 instead. Lastly, I have tried this again with another set of parameters for the column values and to give an output of '3' but this has not worked at all either. 

I have checked that all columns are small integer so there is no datatype error and mapinfo is running the process fine but not returning the result I would like, if any at all. Please help!
Thanks,
Hannah

Hi Hannah
Without getting into the validity of your syntax, I would approach this differently.
I would perform the relevant SQL query resulting in Query1. Then update Query1 (the results) with the value of 1.
i.e. you are only updating the results of your query and not the whole table.
Repeat for value of 2 and of 3.
If you find Column D has a value of 1 already populated in Query2 or 3, then you know your query isn't quite right...

Does that make sense?

Hi,

I have a table with a blank column I would like to fill based on values in other columns from the same table.

For example 
If Column A, B and C contain the value '1', I would like to update Column D (empty column) with the value of '1'.
I have written this in the Update Column expression box as:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=1) = 1
Result: Columns D = 1 (where other column value criteria is met)
This seems to populate the correct '1' value in the correct rows. 

However, when I alter this slightly to populate the value '2' mapinfo populates the value of '1' again.
For this expression I wrote:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=-1) = 2
Result: Column D = 1 (where other column value criteria is met)
Desired result: Column D = 2 (where other column value criteria is met)
For the second expression I would like Column D to have a value of 2 where those criteria are met. I don't understand why it is populating a value of 1 instead. Lastly, I have tried this again with another set of parameters for the column values and to give an output of '3' but this has not worked at all either. 

I have checked that all columns are small integer so there is no datatype error and mapinfo is running the process fine but not returning the result I would like, if any at all. Please help!
Thanks,
Hannah

Hi,

I have a table with a blank column I would like to fill based on values in other columns from the same table.

For example 
If Column A, B and C contain the value '1', I would like to update Column D (empty column) with the value of '1'.
I have written this in the Update Column expression box as:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=1) = 1
Result: Columns D = 1 (where other column value criteria is met)
This seems to populate the correct '1' value in the correct rows. 

However, when I alter this slightly to populate the value '2' mapinfo populates the value of '1' again.
For this expression I wrote:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=-1) = 2
Result: Column D = 1 (where other column value criteria is met)
Desired result: Column D = 2 (where other column value criteria is met)
For the second expression I would like Column D to have a value of 2 where those criteria are met. I don't understand why it is populating a value of 1 instead. Lastly, I have tried this again with another set of parameters for the column values and to give an output of '3' but this has not worked at all either. 

I have checked that all columns are small integer so there is no datatype error and mapinfo is running the process fine but not returning the result I would like, if any at all. Please help!
Thanks,
Hannah

Hi Hannah

I think what you see is MapInfo Pro assigning a true/false or 1/0 value based on your expressions.

You can use John's approach and run multiple queries and update the table through these. Or you can use one of these approaches:

IIf
The IIf function is rather new to MapInfo Pro. It allows you to get one of two values returned based on a condition you pass to the function as well.

IIf(condition, true_value, false_value)

If the condition_expr is true, the true_value will be returned. Otherwise, the false_value will get returned.

Your expression in the Update Column Expression could look like this:
IIf((ColumnA = 1 or ColumnA = 6) and (ColumnB = 1 or ColumnB = 6) and (ColumnC = -1), 2, 0)

You might also use this condition where the current value of your column named ColumnD would be maintained:
IIf((ColumnA = 1 or ColumnA = 6) and (ColumnB = 1 or ColumnB = 6) and (ColumnC = -1), 2, ColumnD)

Where Condition
You can also a different approach which was made available through the SQL improvement we made in MapInfo Pro v2019. The Update statement now allows you to use Where expressions to control what records to update. This allows you to update parts of a table without running queries first, see John's suggestion.

To do this, you will however have to use the SQL Window as the standard Update Column dialog doesn't have a way for you to add a Where condition.

Your statement could look like this:
Update Table ABCD
Set ColumnD = 2
Where (ColumnA = 1 or ColumnA = 6) and (ColumnB = 1 or ColumnB = 6) and (ColumnC = -1)

HTH

Hi,

I have a table with a blank column I would like to fill based on values in other columns from the same table.

For example 
If Column A, B and C contain the value '1', I would like to update Column D (empty column) with the value of '1'.
I have written this in the Update Column expression box as:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=1) = 1
Result: Columns D = 1 (where other column value criteria is met)
This seems to populate the correct '1' value in the correct rows. 

However, when I alter this slightly to populate the value '2' mapinfo populates the value of '1' again.
For this expression I wrote:
(Column A=1 or Column A=6) and (Column B=1 or Column B=6) and (Column C=-1) = 2
Result: Column D = 1 (where other column value criteria is met)
Desired result: Column D = 2 (where other column value criteria is met)
For the second expression I would like Column D to have a value of 2 where those criteria are met. I don't understand why it is populating a value of 1 instead. Lastly, I have tried this again with another set of parameters for the column values and to give an output of '3' but this has not worked at all either. 

I have checked that all columns are small integer so there is no datatype error and mapinfo is running the process fine but not returning the result I would like, if any at all. Please help!
Thanks,
Hannah