Need to update multiple columns using another table
831561Jul 31 2011 — edited Jul 31 2011I have 2 tables. and i need to update rows of 1 table using another table
Table1
Serial_no. payment_date Payment_amt
101 22/11/2010 150
101 18/03/2011 355
102 15/04/2011 488
103 20/05/2011 178
102 14/06/2011 269
101 28/06/2011 505
Table2
Serial_no Charge_amt Last_paymt_dt Last_paymt_amt
101 255
102 648
103 475
I want to update Last_paymt_dt and Last_paymt_amt of table2 using Table1, I have written following update statement but it gives error that single row subquery return multiple row.
Update Table2
set (Last_paymt_dt,Last_paymt_amt) = (select max(payment_date, payment_amt) from table1
where table1.Serial_no = table2.Serial_no group by payment_amt)
kindly suggest how should i update.