12 Replies Latest reply: Feb 21, 2014 10:35 AM by Partha Sarathy S RSS

Update the data from Different table

user4393463 Newbie
Currently Being Moderated

These are my Two tables, Table A

 

 

IDHIST_FLGREVIEW_DATE
1000611/6/2012 11:14
100053/14/2013 11:10
10004
10003
10002
10001
10000
200046/11/2008 10:20
20003
20002
20001
20000

 

And Table B

IDHIST_FLGCodeStatus_Date
10000483/29/2013
10001453/21/2013 9:06
10002443/14/2013 11:33
10003443/14/2013 11:10
20000517/22/2008
20001476/23/2008 13:18
20002446/11/2008 10:20

 

I am trying to Update Table A all the  REVIEW_DATE  == NULL and HIST_FLAG should be less than the existing NOT NULL VALUES for REVIEW_DATE With the MAX(Status_date) from TABLE B WHERE Code = 44.

 

My FInal Result Should be

IDHIST_FLGREVIEW_DATE
1000611/6/2012 11:14
100053/14/2013 11:10
100043/14/2013 11:33
10003      3/14/2013 11:33
10002      3/14/2013 11:33
10001      3/14/2013 11:33
10000     3/14/2013 11:33
200046/11/2008 10:20
20003      6/11/2008 10:20
20002      6/11/2008 10:20
20001     6/11/2008 10:20
20000     6/11/2008 10:20

 

This is what I Tried

UPDATE TABLE_A A

SET A.REVIEW_DATE = (SELECT MAX(B.STATUS_DATE) FROM TABLE_B B  WHERE A.ID = B.ID AND B.CODE = '44')

WHERE EXISTS( SELECT 1 FROM TABLE_B B WHERE WHERE A.ID = B.ID)

AND A.ID IN( SELECT A.ID  FROM TABLE_A A

  WHERE A.REVIEW_DATE IS NULL

  AND A.ID in ( SELECT B.ID FROM TABLE_B B WHERE B.CODE ='44')

AND A.HIST_FLAG < SELECT  MIN(A.HIST_FLAG) -- I dont know what to do here  ;

 

Is this possible with a single update. Thanks in advance.

  • 1. Re: Update the data from Different table
    Partha Sarathy S Guru
    Currently Being Moderated

    Like this?

     

    UPDATE TABLE_A A

    SET REVIEW_DATE = (SELECT MAX(STATUS_DATE) FROM TABLE_B B WHERE CODE=44

                        AND A.ID=B.ID

                        AND B.HIST_FLAG < ( SELECT MAX(HIST_FLAG) FROM (

                                           SELECT ID,HIST_FLAG FROM TABLE_A  WHERE REVIEW_DATE IN (

                                          SELECT LAG(REVIEW_DATE IGNORE NULLS)OVER(PARTITION BY ID

                                                              ORDER BY HIST_FLAG DESC) LG FROM TABLE_A)) AA

                                          WHERE AA.ID=B.ID GROUP BY ID)

    GROUP BY ID)

    WHERE A.REVIEW_DATE IS NULL

    AND EXISTS (SELECT 1 FROM TABLE_B B WHERE CODE=44

                        AND A.ID=B.ID  GROUP BY ID);

     

    OUTPUT:

     

    1000 6 11/06/2012 11:14
    1000 5 03/14/2013 11:10
    1000 4 03/14/2013 11:33
    1000 3 03/14/2013 11:33
    1000 2 03/14/2013 11:33
    1000 1 03/14/2013 11:33
    1000 0 03/14/2013 11:33
    2000 4 06/11/2008 10:20
    2000 3 06/11/2008 10:20
    2000 2 06/11/2008 10:20
    2000 1 06/11/2008 10:20
    2000 0 06/11/2008 10:20
  • 2. Re: Update the data from Different table
    AnnPricks E Guru
    Currently Being Moderated

    Hi.. Try the below and let me know in case of any issues

    UPDATE tablea aa

    SET review_date = (SELECT MAX(status_date)

                       FROM tableb b

                       WHERE aa.id = b.id

                       AND code = 44)

    WHERE aa.review_date IS NULL

          AND aa.hist_flag <ALL (SELECT hist_flag

                                FROM tablea a

                                WHERE aa.id = a.id

                                AND a.review_date IS NOT NULL);

    (OR)

    -- Merge is more efficient than update

     

    MERGE INTO tablea aa

    USING (SELECT a.id,

                  a.hist_flag,

                  (SELECT MAX(status_date)

                   FROM tableb b

                   WHERE a.id = b.id

                   AND code = 44) mx_date

          FROM tablea a

          WHERE a.review_date IS NULL

          AND a.hist_flag <ALL (SELECT hist_flag

                                FROM tablea aa

                                WHERE aa.id = a.id

                                AND aa.review_date IS NOT NULL)) q1

    ON(aa.id = q1.id

       AND aa.hist_flag = q1.hist_flag)

    WHEN MATCHED THEN

    UPDATE SET review_date = mx_date;


    OUTPUT:-

    -------

     

            ID  HIST_FLAG TO_CHAR(REVIEW_DATE,'DD-MON-Y

    ---------- ---------- -----------------------------

          1000          6 06-NOV-2012 11:14:00

          1000          5 14-MAR-2013 11:10:00

          1000          4 14-MAR-2013 11:33:00

          1000          3 14-MAR-2013 11:33:00

          1000          2 14-MAR-2013 11:33:00

          1000          1 14-MAR-2013 11:33:00

          1000          0 14-MAR-2013 11:33:00

          2000          4 11-JUN-2008 10:20:00

          2000          3 11-JUN-2008 10:20:00

          2000          2 11-JUN-2008 10:20:00

          2000          1 11-JUN-2008 10:20:00

          2000          0 11-JUN-2008 10:20:00

     

    12 rows selected.

     

  • 3. Re: Update the data from Different table
    user4393463 Newbie
    Currently Being Moderated

    Thank you Parth and Ann for your quick reply.

    Ann,

    I will run your SQL ASAP and Let you know.

     

    Parth,

    I think I did not put my requirement properly. It has nothing to do with the HIST_FLAG in TABLE_B . Get the MAX( Status_date) from TABLE_B for the Code = 44 and Update the REVIEW_DATE in TABLE_A for the ID for all the records where HIST_FLAG in TABLE_A is less .

    for the ID( TABLE_A) 1000, it should update  REVIEW_DATE for only HIST_FLAG in (4,3,2,1,0) REVIEW_DATE is not null for (ID,HIST_FLAG) (1000,5). Update all the records less than HIST_FLAG=5. Your query updates all the records Where REVIEW_DATE IS NULL for the corresponding ID.

    There Could be a scenario in TABLE_A. The update should not affect the HIST_FLAG 7 and 8 for the ID =1000. Thanks a lot for helping me .

     

    IDHIST_FLGREVIEW_DATE
    10008
    10007
    1000611/6/2012 11:14
    100053/14/2013 11:10
    10004
    10003
    10002
    10001
    10000
    200046/11/2008 10:20
    20003
    20002
    20001
    20000
  • 4. Re: Update the data from Different table
    AnnPricks E Guru
    Currently Being Moderated

    Sure. Try that and let me know in case of any issues.

  • 5. Re: Update the data from Different table
    Partha Sarathy S Guru
    Currently Being Moderated

    Yes i think used the same logic you have mentioned. Try it and check whether it is working as expected

  • 6. Re: Update the data from Different table
    user4393463 Newbie
    Currently Being Moderated

    Thanks a lot Ann. The solution which you gave me is just perfect. Thanks a lot.

     

    Do you have any suggestion for any books for advanced Oracle SQL.

  • 7. Re: Update the data from Different table
    user4393463 Newbie
    Currently Being Moderated

    Parth,

     

    The  UPDATE Staement you gave me is updating the HIST_FLAG 7 and 8 for the ID =1000 where it should update only those records where HIST_FLAG<5

  • 8. Re: Update the data from Different table
    AnnPricks E Guru
    Currently Being Moderated

    Just read oracle documentation. You can just download it here

    Re: 1. Where can I find Oracle Documentation?

  • 9. Re: Update the data from Different table
    AnnPricks E Guru
    Currently Being Moderated

    If your doubt is addressed, please mark the thread as answered so members can focus on other threads

  • 10. Re: Update the data from Different table
    Partha Sarathy S Guru
    Currently Being Moderated

    Fine. Made a few modifications. Try this.

     

    UPDATE TABLE_A A

    SET REVIEW_DATE = (SELECT MAX(STATUS_DATE) FROM TABLE_B B WHERE CODE=44

                        AND A.ID=B.ID

                        )

    WHERE A.REVIEW_DATE IS NULL

    AND A.HIST_FLAG < (SELECT MIN(HIST_FLAG) FROM TABLE_A B

                          WHERE A.ID=B.ID

                          AND REVIEW_DATE IS NOT NULL);

     

    OUTPUT:

     

    1000 8
    1000 7
    1000 6 11/06/2012 11:14
    1000 5 03/14/2013 11:10
    1000 4 03/14/2013 11:33
    1000 3 03/14/2013 11:33
    1000 2 03/14/2013 11:33
    1000 1 03/14/2013 11:33
    1000 0 03/14/2013 11:33
    2000 4 06/11/2008 10:20
    2000 3 06/11/2008 10:20
    2000 2 06/11/2008 10:20
    2000 1 06/11/2008 10:20
    2000 0 06/11/2008 10:20
  • 11. Re: Update the data from Different table
    user4393463 Newbie
    Currently Being Moderated


    Thanks a lot Parth. That worked.

     

    and again thanks a lot Ann and Parth for solving this. you guys really rocks..

  • 12. Re: Update the data from Different table
    Partha Sarathy S Guru
    Currently Being Moderated

    You are welcome

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points