Complex Update query

[kalindi05]

Hello everyone,

Please help me revise this query to make it work. I don't know much about update query so I will need some help with it. Thanks,


update encounter
set mergedpayment = totalactualcashpayments
from encounter E, paymentITEM P, encounterfinancialrecord F
where (E.encounterid = P.FINANCIALRECNUM) AND (E.encounterid = F.FINANCIALRECNUM) and (transtypecode = 'C') and (F.INSPLANCODE not in ('UNINSURED', 'SP', 'Selfpay'));

[cweiss]

Try this:

UPDATE encounter E
SET E.mergedpayment = (
    SELECT <TableAlias>.totalactualcashpayments
    FROM 
       paymentITEM P, 
       encounterfinancialrecord F
    WHERE
       E.encounterid = P.financialrecnum AND
       E.encounterid = F.financialrecnum AND
    <TableAlias>.transtypecode = 'C' AND 
       F.insplancode NOT IN('UNINSURED', 'SP', 'Selfpay'));

[kalindi05]

It worked!! Thank you so much.

Try this:

UPDATE encounter E
SET E.mergedpayment = (
    SELECT <TableAlias>.totalactualcashpayments
    FROM 
       paymentITEM P, 
       encounterfinancialrecord F
    WHERE
       E.encounterid = P.financialrecnum AND
       E.encounterid = F.financialrecnum AND
    <TableAlias>.transtypecode = 'C' AND 
       F.insplancode NOT IN('UNINSURED', 'SP', 'Selfpay'));