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

------------------------------
Hannah Alred
Knowledge Community Shared Account
------------------------------

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?

------------------------------
John Ievers
CDR Group
Hope Valley, United Kingdom
------------------------------

Hi John,

Thanks for your reply. Yes, I did think I needed to break it down as such and I've managed to get it all correct now - thanks for your help!

Hannah

------------------------------
Hannah Alred
Knowledge Community Shared Account
------------------------------

Original Message:
Sent: 05-11-2023 11:38
From: John Ievers
Subject: Update column using expression

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?

------------------------------
John Ievers
CDR Group
Hope Valley, United Kingdom
------------------------------

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

Hi Peter,

Thanks for your reply. Really helpful to hear about how the new IIf function works and see a worked example. I have had a play around with it and got it to work as well so it was a good suggestion, I prefer it over the where condition in SQL! Also thanks for pointing out 'You might also use this condition where the current value of your column named ColumnD would be maintained', otherwise all my previous expressions would've been undone!

Hannah

------------------------------
Hannah Alred
Knowledge Community Shared Account
------------------------------

Original Message:
Sent: 05-12-2023 03:00
From: Peter Møller
Subject: Update column using expression

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