Update using 2 tables

[yxez]

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

[PAVB]

Something like this perhaps?

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

[yxez]

wow! that was fast...thanks a lot

[yxez]

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

[PAVB]

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!

[yxez]

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 )
;

[PAVB]

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.

[yxez]

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

[PAVB]

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.