tsql - Problem with using max in the below update statement -

tsql - Problem with using max in the below update statement -

hi i'm having problem using max statement in below query working on

update tb_ts_cm_ofirecpt set amt = (coalesce (abs(cmledg.tranamt), 0) + coalesce (abs(cmledg_1.tranamt), 0)) * - 1, tranamt = abs(cmledg.tranamt) *-1, rtaxamt = coalesce (abs(cmledg_1.tranamt) * - 1, 0), tranid = cmledg.tranid tb_ts_cm_ofirecpt inner bring together cmledg on tb_ts_cm_ofirecpt.tranid = cmledg.refnmbr , tb_ts_cm_ofirecpt.descrptn = cmledg.descrptn left outer bring together cmledg cmledg_1 on cmledg.tranid = cmledg_1.parenttranid , cmledg.descrptn = cmledg_1.descrptn (tb_ts_cm_ofirecpt.impid = '195') , (tb_ts_cm_ofirecpt.action = 'reversed') tranid = cmledg.tranid tb_ts_cm_ofirecpt inner bring together cmledg - particualr line should tranid = select max(tranid) ....

however kerep getting syntax error. appreciate yuor help

regards,

abhi

not exclusively sure try:

tranid = (select max(tranid) ... ... inner bring together ... ),

the brackets may plenty allow compiler grouping sub query nicely before assigning value.

if can create views find much easier define view calculated fields , create update plain possible. separation makes easier work out real problem lies.

tsql