Update using 2 tables

Hi Friends,


I have two tables LOANS AND PAYMENTS

LAONS
=====
EMPID NUMBER(5),
LOAN_TYPE VARCHAR2(20),
LOAN_DATE DATE,
LOAN_AMOUNT NUMBER(7,2),
STATUS VARCHAR2(10)

PAYMENTS
========
EMPID NUMBER(5),
LOAN_TYPE VARCHAR2(20),
PAYMENT_DATE DATE,
PAYMENT_AMOUNT NUMBER(5,2)


LOANS
=====

EMPID LOAN_TYPE LOAN_DATE LOAN_AMOUNT STATUS
===== ========= ========= =========== ======
111 SALARY 01-JAN-06 1000


PAYMENTS
========

EMPID LOAN_TYPE PAYMENT_DATE PAYMENT_AMOUNT
===== ========= ============ ==============
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100
111 SALARY 01-FEB-07 100


I want to update the LOANS.STATUS = 'PAID'
IF SUM(PAYMENT_AMOUNT) >= LOAN_AMOUNT WHERE LOANS.EMPID=PAYMENTS.EMPID
AND LOANS.LOAN_TYPE=PAYMENTS.LOAN_TYPE


can you help me the syntax pls....thanks

Something like this perhaps?

Code:

---

update loans l
set    l.status = 'PAID'
where  l.loan_mount <=
          (
          select  sum(p.payment_amount)
          from    payments p
          where    l.empid = p.empid
          and      l.loan_type = p.loan_type
          group by p.empid,p.loan_type
          )
;

---

Note: Code was not tested, sorry, have real work on my plate :)

wow! that was fast...thanks a lot:)

Hi Pavb,

Another one pls...

LAONS
=====
EMPID NUMBER(5),
LOAN_TYPE VARCHAR2(20),
LOAN_DATE DATE,
LOAN_AMOUNT NUMBER(7,2),
PAYMENT_TERM NUMBER, #in days
STATUS VARCHAR2(10)

PAYMENTS
========
EMPID NUMBER(5),
LOAN_TYPE VARCHAR2(20),
PAYMENT_DATE DATE,
PAYMENT_AMOUNT NUMBER(5,2)


LOANS
=====

EMPID LOAN_TYPE LOAN_DATE LOAN_AMOUNT Payment_term STATUS
===== ========= ========= =========== ============ ======
111 SALARY 01-JAN-06 1200 365


PAYMENTS
========

EMPID LOAN_TYPE PAYMENT_DATE PAYMENT_AMOUNT
===== ========= ============ ==============
111 SALARY 01-FEB-07 100
111 SALARY 01-MAR-07 100
111 SALARY 01-APR-07 100
111 SALARY 01-MAY-07 100
111 SALARY 01-JUN-07 100
111 SALARY 01-JUL-07 100
111 SALARY 01-AUG-07 100
111 SALARY 01-SEP-07 100
111 SALARY 01-OCT-07 100
111 SALARY 01-NOV-07 100
111 SALARY 01-DEC-07 100
111 SALARY 01-JAN-07 100



I want to update the LOANS.STATUS = 'PAID WITHIN REQUIRED TERMS'
IF (LAST PAYMENT_DATE - LOAN_DATE) <= PAYMENT_TERM
WHERE LOANS.EMPID=PAYMENTS.EMPID
AND LOANS.LOAN_TYPE=PAYMENTS.LOAN_TYPE


can you help me the syntax pls....thanks

Don't be that lazy.
Now you already know all you need is an inline view to solve the problem.
You can solve it in less than 5 minutes, go for it!

I tried it but it hanged up.... :(

update loans l set l.status='PAID WITHIN REQUIRED TERMS'
where l.payment_term>=
(select max(p.payment_date)
from payment p
where l.empid = p.empid
and l.loan_type = p.loan_type
- l.loan_date )
;

So you are trying, I like that.

1- You cannot compare a number like payment_term with a date like payment_date.
The first term of your predicate should be a date like (loan_date + payment_term)

2- You can't substract a date like loan_date from a varchar like loan_type; actually not sure why you added that "- l.loan_date" at the end of your predicate, just take it away.

You are almost there.

Hi,

I got another 2 table

ENTRY
(name,date_entered,days_allowed,status)

NAME DATE_ENTRY DAYS_ALLOW STATUS
===== ========== ========== =======
SCOTT 01-JAN-07 30

EXIT
(NAME,DATE_EXIT)

NAME DATE_EXIT
===== ========
SCOTT 01-JUL-07


I want to update status = 'OVERSTAY'
if date_exit - date_entry > days_allow


update ENTRY a set a.status='OVERSTAYING'
where a.days_allow< (select b.date_exit - a.date_entry from EXIT b
where a.name = b.name);


Is the above code correct? I hangs up updating 100,000 ENTRY against 100,000 EXIT rows.


Thanks a lot

I do not think it's hanging; most likely taking too long to complete.

Let me guess... you do not have any index on NAME column from EXIT table, am I right?

Please take a look at your explain plan, what it shows?
...perhaps Nested loops and Full Table Scan on EXIT table?

Build a non-unique index on EXIT.NAME, gather fresh stats on both tables, cascade then, try again.