Update using 2 tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Update using 2 tables

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    wow! that was fast...thanks a lot
    Behind The Success And Failure Of A Man Is A Woman

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    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

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width