update the largest amount payor 1

[yan19454]

I have one table which the payment from multiple insurance company and another table contain the patient information. I want to update the patient information with the insurance company information who paid the most bill.
table one:
PID Payor
111
222
333
I need to update the table one with payor as EDE since they paid the largest amount. Thanks.
table two
PID Payor PayAmount
111 MIC 10.00
111 ACB 110.00
111 EDE 1110.00
223 MIC 10.00
121 ACB 110.00
144 EDE 14500.00


I do not want to set up the recordset as I did in the vb application and update the record. Thanks.

 

[yan19454]

SELECT rptPXA3.Payor AS Expr1, max(rptPXA3.AnCharge) AS Expr2
FROM InpatientMedStat INNER JOIN
rptPXA3 ON InpatientMedStat.PatientAccountNumber = rptPXA3.PNO group by rptPXA3.Payor , rptPXA3.PNO
GO

I got the select part work. I do not now to translate to update query something like below . Thanks.


update InpatientMedStat set InsurancePlanCode= rptpxa3.payor (select Max(rptPXA3.AnCharge), rptPXA3.Payor FROM rptPXA3 INNER JOIN
InpatientMedStat ON InpatientMedStat.PatientAccountNumber = rptPXA3.PNO )

 

[bborissov]

UPDATE Table1 SET PayOr = Tbl1.Payor
FROM Table1
INNER JOIN (SELECT PID, Tbl2.Payor
                   FROM Table2
                   INNER JOIN (SELECT Pid, MAX(PayAmount) AS Amt
                                      FROM Table2
                                      GROUP BY Pid) Tbl2
                   ON Table2.Pid       = Tbl2.Pid AND
                      Table2.PayAmount = Tbl2.Amt) Tbl1
     ON Table1.PID = Tbl1.Pid

not tested


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[yan19454]

Thanks. I see you use derive table to join.

 

[yan19454]

The query is so complex for me to compenhend. Do you know easier way ? Thanks.