iif statement example???

[dkingston]

thanks to jessestoneceder for your previous help

i'm trying to combine multiple update queries (1 for each
field in a table) to a single update query.
the SQL for my update queries is:
UPDATE MSSECDES SET MSSECDES.DES2 = Null
WHERE (((MSSECDES.DES2) Like ' *'));

and:
UPDATE MSSECDES SET MSSECDES.DES3 = Null
WHERE (((MSSECDES.DES3) Like ' *'));

etc.

i'm guessing the new SQL would be something like:
UPDATE MSSECDES
IIF (((MSSECDES.DES2) Like ' *')) THEN
SET MSSECDES.DES2 = Null
ENDIF
IIF (((MSSECDES.DES3) Like ' *')) THEN
SET MSSECDES.DES3 = Null
ENDIF
etc.

this code does not work.
can anyone give me an example of what the SQL should be?

many thanks in advance.
please reply via post or to:
d k i n g s t o n (at) j a g l y n n (dot) c o m

 

[Tim Ferguson]

this code does not work.
can anyone give me an example of what the SQL should be?

Relational theory is bound up in set theory: since each of these commands
refers to a different set of records then you'll need to do them
separately.

You could do a really horrid set of IIF() functions but it would be awful
to debug and maintain, and probably a lot slower.

You could set the query up as a parameter query and call it in a loop: that
is almost certainly the safest and quickest way.

Hope that helps


Tim F

 

[david epsom dot com dot au]

update msssecdes set
a = iif([a] like ' *',null,[a]),
b = iif( like ' *',null,)
etc

(david)