-
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
Behind The Success And Failure Of A Man Is A Woman
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
wow! that was fast...thanks a lot
Behind The Success And Failure Of A Man Is A Woman
-
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
Last edited by yxez; 07-26-2007 at 12:31 PM.
Behind The Success And Failure Of A Man Is A Woman
-
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!
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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 )
;
Behind The Success And Failure Of A Man Is A Woman
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
joined table confused
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
Behind The Success And Failure Of A Man Is A Woman
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|