updated based on a condition

  • Thread starter Thread starter regrets
  • Start date Start date
R

regrets

is there a single update statement that can be executed in design or sql mode
that can handle this situation. Or does it take multiple update statements to
achieve what i want?

update flda
to '11' if fldb = x
to '12' if fldb = y
tp '13' if fld b = 'c'

Thanks
Any help appreciated
 
is there a single update statement that can be executed in design or sql mode
that can handle this situation. Or does it take multiple update statements to
achieve what i want?

update flda
to '11' if fldb = x
to '12' if fldb = y
tp '13' if fld b = 'c'

Thanks
Any help appreciated
Click to expand...
Your example is confusing. What datatypes are involved here?
You have the update to values in quotes, '11', '12', '13', so they
are Text.
Next you have the criteria = x and = y so those criteria values are
Number.Then you have ='c' so now where back to a text value.

I'll guess that FieldA is a number datatype, i.e. 11, 12, 13, and the
criteria FieldB is a text datatype, i.e. = 'x', 'y', or 'c', and that
those 3 criteria are the only criteria that will appear.

Back up your table first.
Run an Update query:

Update YourTable Set YourTable.[FieldA] = IIf([FieldB] =
"x",11,IIf([FieldB]="y",12,13))
 
is there a single update statement that can be executed in design or sql mode
that can handle this situation. Or does it take multiple update statements to
achieve what i want?

update flda
to '11' if fldb = x
to '12' if fldb = y
tp '13' if fld b = 'c'
Click to expand...


Update flda to:

Switch([fldb] = "X","11", [fldb] = "y", "12", [fldb] = "c", "13")

If you'll have more than five or ten values, consider using a translation
table with two fields; remove flda from your table altogether and just join
the translation table on fldb.
 
Back
Top