Oracle: Update two columns from other table having max date

305 pts.
Tags:
Oracle
Oracle Query
Here's what I have so far:
UPDATE table2

SET (DATE, ENTRAMNT) = (SELECT MAX ( DATE),

 (B.ENTRAMNT)

FROM TABLE1 B

WHERE B.ACCTNUMR =TABLE1. ACCTNUMR

WHERE EXISTS (SELECT *

FROM TABLE1 B

WHERE B.ACCTNUMR = TABLE1.ACCTNUMR)
This does not work.


Software/Hardware used:
oracle 7.3.1
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

This should do what you want:

UPDATE table2 t2
SET  ("date", entramnt)  =
  (SELECT t1."date", t1.entramnt
    FROM table1 t1
    JOIN
      (SELECT acctnumr, MAX("date") AS "date"
        FROM table1
        GROUP BY acctnumr) l
    ON t1.acctnumr = l.acctnumr
      AND t1."date" = l."date"
    WHERE t1.acctnumr = t2.acctnumr
  );

The subquery is used to get the latest transaction for each account, and then it is joined to table1 to get the amount of the latest transaction.

See discussion below for variations of this query for older versions of the database.

Discuss This Question: 24  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Not sure what you are trying to do:

    In your first subquery, since you are using an aggregate function (max) along with another column you need an ORDER BY clause.

    In addition to that, you appear to have 2 WHERE clauses in your main query.

    If you need more help, please post your code again, and post the exact error message you are getting, or tell us exactly what you mean by "does not work".


    84,720 pointsBadges:
    report
  • 235235235
    i have records like this in table1(i have more than 5000 acctnumr in table1 having payment date and amount)

    acctnumr     date      entramnt
    0100221   01-dec-15      1500
    0100221   01-jan-16      2000
    0100221   01-feb-16      900
    0100222   01-oct-15      3500
    0100222   01-sep-15      500
    0100223   01-oct-15      1000
    i want to update another table2 to selecting last payment date and amoun
    305 pointsBadges:
    report
  • 235235235
    my table2 is like this 

    acctnumr     date      entramnt
    0100221   
    0100222
    0100223   
    0100224   
    0100225 
    I want to post last payment and date from table1 to table2
    Hope you understand my problems thank for your response.
    305 pointsBadges:
    report
  • 235235235
    thank for your deep concern i will try and respond soon.
    305 pointsBadges:
    report
  • 235235235
    hello sir, the query you sent gives error "right parentheses error"
    on JOIN key word 
    305 pointsBadges:
    report
  • carlosdl
    What version of Oracle are you using?  8i or older?

    If that is the case, you need to use the old syntax for joins:

    UPDATE table2 t2
    SET  ("date", entramnt)  =
      (SELECT t1."date", t1.entramnt
        FROM table1 t1,
          (SELECT acctnumr, MAX("date") AS "date"
            FROM table1
            GROUP BY acctnumr) l
        WHERE t1.acctnumr = l.acctnumr
          AND t1."date" = l."date"
          AND t1.acctnumr = t2.acctnumr
      );

    84,720 pointsBadges:
    report
  • 235235235
    7.3.1 i am using.
    305 pointsBadges:
    report
  • carlosdl
    So, did you try my last suggestion?
    84,720 pointsBadges:
    report
  • 235235235
    Not yet Sir, My working hours are over i will reply you tomorrow.Our working hours from 8 am to 3 pm including saturday . anyway thanks you very much.
    305 pointsBadges:
    report
  • 235235235
    Now here the time is 5.20 pm.
    305 pointsBadges:
    report
  • 235235235
    new query shows "ORA-01427: single-row subquery returns more than one row"
    305 pointsBadges:
    report
  • carlosdl
    Then it means that for some account you have more than one transaction with the date, which is the max date. How would you like to proceed in that case? What criteria must be used to decide which one's values should be used in the update?
    84,720 pointsBadges:
    report
  • 235235235
     if it is so, the entries will be like as under in table1
     (see last entries pl. )

    acctnumr    date      entramnt Sr.No. batch No.
    0100220   01-dec-15     1500    1       1590
    0100221   01-dec-15     200      2       1590
    0100221   01-jan-16     2000   10      1610
    0100221   01-feb-16      900     1         1625  
    0100222   01-oct-15      3500    99       1575
    0100222   01-sep-15      500     8         1560
    0100223   01-oct-15      1000    1        1575
    0100224   01-oct-15      1000    2        1575
    0100223   01-oct-15      1000    3        1575
    0100225   01-oct-15      1000    4         1575
    only sr.no differes. Every date collection start with Sr.no 1.
    this is how we entered in main table through barcode(as above)
    305 pointsBadges:
    report
  • carlosdl
    So, repeating the question from my previous post:  What criteria must be used to decide which one's values should be used in the update?
    84,720 pointsBadges:
    report
  • 235235235
     last date and amount is needed against each acctnumr to see when the customer made last payment.
     Can we not group date and amount if the customer made double payment? 
    305 pointsBadges:
    report
  • carlosdl
    What happens if there are 2 records with the same date (the max date) but with different amounts?  Which amount should be used in the UPDATE?
    84,720 pointsBadges:
    report
  • 235235235
    2nd amount can be consider from sr.no, no matter amount is bigger or small.
    305 pointsBadges:
    report
  • 235235235
    it can only be verified when next bill to customer is issued and if the payment is wrong,double or different  the same can be reversal or corrected.
    305 pointsBadges:
    report
  • carlosdl
    If it is really not important to use one amount or another, then try this:

    UPDATE table2 t2
    SET  ("date", entramnt)  =
    (SELECT "date", entramnt
      FROM
      (SELECT t1.acctnumr,t1."date", t1.entramnt
        FROM table1 t1,
          (SELECT acctnumr, MAX("date") AS "date"
            FROM table1
            GROUP BY acctnumr) l
        WHERE t1.acctnumr = l.acctnumr
          AND t1."date" = l."date"
        ORDER BY t1.acctnumr,entramnt DESC)
      WHERE acctnumr = t2.acctnumr
        AND rownum = 1);
    I don't have your data, so I can't test it, but I believe it will work.

    84,720 pointsBadges:
    report
  • 235235235
    thank you sir, you been very helpful, i will try and inform you on monday. I have some more issues can i discuss with you if it does not bother you?
    305 pointsBadges:
    report
  • 235235235
    UPDATE MOZANG t2
    SET ("SCRLDATE", ENTRAMNT) =
      (SELECT t1."SCRLDATE", t1.ENTRAMNT
        FROM MOZANGRCTHST t1,
    
          (SELECT ACCTNUMR, MAX ("SCRLDATE") AS "SCRLDATE"
            FROM MOZANGRCTHST
            GROUP BY ACCTNUMR) L
        WHERE t1.ACCTNUMR = L.ACCTNUMR
        AND t1."SCRLDATE" = L."SCRLDATE"
        AND t1.ACCTNUMR = t2.ACCTNUMR 
        AND ROWNUM=1)
    
    
    THE ABOVE MENTIONED QUERY WORKED EXACTLY, THANK FOR YOUR CO-OPERATION I HAVE SOME MORE ISSUES CAN I DISCUSS IT WITH YOU?
    305 pointsBadges:
    report
  • carlosdl
    Good to know it worked!

    Yes, we can discuss them.  Just make sure to create a new question for each one, and provide as much detail as possible.

    84,720 pointsBadges:
    report
  • 235235235
    Sir, as I mentioned earlier that I'm using Oracle 7.3.1 and Forms 2.1 for data entry and other relating reports. SQL Worksheet is open for every user. every user can use SQL Worksheet although they dont have DBA knowledge. they can use select, update, delete and insert key words. Can we restrict them by using these?
    305 pointsBadges:
    report
  • carlosdl
    I don't think it is a good idea to let users interact with the database directly, but if that cannot be changed, all of the database objects should be owned by a different user, so that your users only have the privileges you grant them on the tables and other database objects specifically.
    84,720 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: