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
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------
Original Message:
Sent: 05-11-2023 11:25
From: Hannah Alred
Subject: Update column using expression
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
------------------------------
Hannah Alred
Knowledge Community Shared Account
------------------------------