Updating multiple rows

[Larus]

Hello,

I have a table like this in my db:

id number amount
X-0006 3 65
X-0054 2 678
X-0974 5 6345
X-0023 6 62
....
15620 rows

I'm trying to update multiple rows in this temptable using iif-
statement:

UPDATE temptable
SET temptable.id = IIf(temptable.id="X-0006","E-155155",
IIf(temptable.id="X-0010","E-161617",
IIf(temptable.id="X-0011","E-161535",
IIf(temptable.id="X-0015","E-156167",
IIf(temptable.id="X-0016","E-198120",
IIf(temptable.id="X-0019","E-198495",
IIf(temptable.id="X-0021","E-212138",
IIf(temptable.id="X-0022","E-872048",
IIf(temptable.id="X-0023","E-191698",
IIf(temptable.id="X-0024","E-215212",
IIf(temptable.id="X-0025","E-703364",
IIf(temptable.id="X-0027","E-222125",
IIf(temptable.id="X-0028","E-220204",
IIf(temptable.id="X-0030","E-224038",
IIf(temptable.id="X-0031","E-200361",
IIf(temptable.id="X-0032","E-201000",
IIf(temptable.id="X-0033","E-222166",
IIf(temptable.id="X-0034","E-214169",
IIf(temptable.id="X-0035","E-184168",
IIf(temptable.id="X-0036","E-232160",
.....

I would like to select only these X:s (in temptable.id) and update
these with corresponding E:s. How is this possible? This update clause
is not functioning.

Many thanks,
-L

 

[John Spencer]

Since you can only nest a few IIF's (seven comes to mind as the max number)
this is not a good method.

I would use a table of eqivalents and use that.
Table Name: NewValuesTable
Fields: OldValue and NewValue

UPDATE SomeTable INNER JOIN NewValuesTable
On SomeTable.ID = NewValuesTable.OldValue
SET SomeTable.ID = [NewValuesTable].NewValue

You other choice would be to write a custom vba function and call it. That
function would look something like
Public Function fGetNewValue(strIN as String)
SELECT Case
Case "X-0006"
fGetNewValue ="E-155155"
Case "X-0010"
fGetNewValue = "E-161617"
Case ...
End Select

End Function


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..