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 = totalactualcash payments
from encounter E, paymentITEM P, encounterfinanc ialrecord F
where (E.encounterid = P.FINANCIALRECN UM) AND (E.encounterid = F.FINANCIALRECN UM) and (transtypecode = 'C') and (F.INSPLANCODE not in ('UNINSURED', 'SP', 'Selfpay'));

[cweiss]

Try this:

UPDATE encounter E
SET E.mergedpayment = (
    SELECT [I]<TableAlias>[/I].totalactualcashpayments
    FROM 
       paymentITEM P, 
       encounterfinancialrecord F
    WHERE
       E.encounterid = P.financialrecnum AND
       E.encounterid = F.financialrecnum AND
    [I]<TableAlias>[/I].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 [I]<TableAlias>[/I].totalactualcashpayments
    FROM 
       paymentITEM P, 
       encounterfinancialrecord F
    WHERE
       E.encounterid = P.financialrecnum AND
       E.encounterid = F.financialrecnum AND
    [I]<TableAlias>[/I].transtypecode = 'C' AND 
       F.insplancode NOT IN('UNINSURED', 'SP', 'Selfpay'));