Re: sql update comand syntax
> I need to update one table based on data in a second. TFM doesn't have
> example exactly that I need. I have tried arranging the syntax in a
> couple of differnt ways, with no luck. I am new to informix but have a
> tight deadline. here it is
>
> update award set
> award.AWARD_AMOUNT = award.AWARD_AMOUNT + award.CARRY_FORWARD
> where award.AWARD_ACCOUNT = mod.MOD_ACCOUNT and
> (mod.MOD_PROCESSING = "2X" or
> mod.MOD_PROCESSING = "CL");
Try the following:
update award set
award.AWARD_AMOUNT = award.AWARD_AMOUNT + award.CARRY_FORWARD
where award.AWARD_ACCOUNT in ( select mod.MOD_ACCOUNT from mod
where (mod.MOD_PROCESSING = "2X"
or mod.MOD_PROCESSING = "CL")) ;
This is using a subquery to select a set of MOD_ACCOUNT(s) from the
table "mod" and then updating the table "award" where the AWARD_ACCOUNT is
in the set returned by the subquery.
Regards - lester
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
#############################################################################