DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sqlplus update program

  1. #1
    Join Date
    May 2002
    Posts
    4
    Hi Friends,

    I have 2 tables LoanMaster (Firstname,Lastname,LoanBal) and LoanPayment (Firstname,Lastname,Payment).

    I want to update LoanMaster using the transaction table LoanPayment, deducting LoanBal with Payment, matching
    corr Names of employee.

    Is this code riqht?

    update LoanMaster x set x.LoanBal=(x.LoanBal - y.Payment)
    where x.Firstname = (select y.Firstname from y.LoanPayment
    where y.Firstname=x.Firstname)
    and x.Lastname=(select y.Lastname from y.LoanPayment
    where y.Lastname=x.Firstname)
    /

    Kindly help please...

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    What I feel personally is that your table relationship is not correct.It should be :

    LoanMaster (
    Customer_id PK,
    Firstname,
    Lastname,
    LoanBal )

    LoanPayment (
    Customer_id,
    Payment)

    Query

    UPDATE LoanMaster x
    SET x.LoanBal=x.LoanBal-(SELECT y.Payment from LoanPayment y
    WHERE x.Customer_id =y.Customer_id );

    Hope it helps...

    Take Care.




  3. #3
    Join Date
    May 2002
    Posts
    4
    Thank you very much nishant,

  4. #4
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    Do create the master-detail relationship and in detail table put some flag so that when master is upadted then flag in detail is set to false.Add y.flg='T' in the where clause.This will give the correct result.You should always first try to make your table structure correctly with all the constraints,then making any kind of query would be very simple.

    Take Care.

  5. #5
    Join Date
    May 2002
    Posts
    4
    Hi nishant,

    What if I have two fields to be updated Loanbal1,Loanbal2
    for Payment1,Payment2...would this be like this?...

    UPDATE LoanMaster x
    SET x.LoanBal1=x.LoanBal1-(SELECT y.Payment1 from LoanPayment y
    WHERE x.Customer_id =y.Customer_id )

    x.LoanBal2=x.LoanBal2-(SELECT y.Payment2 from LoanPayment y
    WHERE x.Customer_id =y.Customer_id );


    Thanks,

    peter

  6. #6
    Join Date
    May 2002
    Posts
    4
    Is it possible to place one where clause for
    the two fields to be updated?

    Thanks

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