PDA

View Full Version : what's the best way to update multiple fields

rimian
06-24-02, 11:12
Hello!

I want to do this [in access]:

UPDATE table SET field1 = "this" WHERE field2 = "that" AND SET field1 = "thisotherthing" WHERE field2 = "thatotherthing"

Before i was creating loops and opening and closing the connection all the time but it seems like a clumsy method.

Regards
RIM

thanks in advance!
alligatorsql.com
06-25-02, 17:21
Hello,

use

UPDATE table SET field1 = DECODE(field2, 'that', 'this', 'thatotherthing', 'thisotherthing');

Hope that helps ?

Regards
Manfred Peter
(Alligator Company(
http://www.alligatorsql.com
alligatorsql.com
06-25-02, 17:30
Hello,

oh sorry ... I didnīt read that little word access. DECODE is for Oracle but perhaps you will find the same command in access ???

Sorry again

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
jaydog96
06-26-02, 14:56
In the QBE grid, you can do the following:

Set up an UPDATE query. Select field1 as the field to be updated. In the Criteria box, enter the following formula:

IIf([field2] = "that","this","thatotherthing")

If you need to test field2 for more than one value, use a nested IIf (see the Help file for assistance).

If you want to do this in code, open a recordset based on the table. Assign field1 and field2 to variables, then use a SELECT CASE statement to test the value of field2 and update field1 accordingly.

Hope it helps,

j-Dog