IIf statement 1

[wdbouk]

I am trying to use the IIF statement to assign new values for a table using the following command
UPDATE [All] SET Field17 = IIf ([Field2]="AC.DB0",151100);
it is working fine
but if i want to use it to assign another value
[Field2]="AZG.DB0",9800);then the previous 151100 values assigned in the first command disappears and become blank. How can I update the Field2 column without elimintaing the previous values assigned in this column
Best
Wd

 

[cranebill]

This is because your updating all the rows. With your IIF in the second command if it does not = AZG.DB0 then the field is set to null.

UPDATE [All] SET Field17 = IIf ([Field2]="AC.DB0",151100,IIf ([Field2]="AZG.DB0",9800);

If you have alot of these you may want to contrain your updates to those records that match your criteria ie: AC.DB0

 

[wdbouk]

Actually, I have other values in Field17 (it is a 167,000 observations column) . The suggested command requires me to put IIF for all the values in the field otherwise they would be null. I am wondering if there is a way to make the update while leaving the other values untouched.

 

[cranebill]

UPDATE your_table SET your_table.field_17 = 151100
WHERE your_table.field_2 = "AC.DB0";