Re: sql update comand syntax

SDowdy@uh.edu (Steve Dowdy) writes: >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. It uses a subquery to select the criterion from the second table to determine the rows to be updated in the first table. The only restriction on using subqueries in UPDATE statements is that they may not refer to columns in the table being updated (correlated subqueries) to prevent a recursive loop. UPDATE award SET award.AWARD_AMOUNT = award.AWARD_AMOUNT + award.CARRY_FORWARD WHERE award.AWARD_ACCOUNT = (SELECT mod.MOD_ACCOUNT FROM mod WHERE mod.MOD_PROCESSING IN ("2X","CL")) ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com Opinions expressed herein are my own.