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

    Update the data from Different table

    user4393463

      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

          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

            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

              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

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

                • 5. Re: Update the data from Different table
                  Partha Sarathy S

                  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

                    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

                      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

                        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

                          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

                            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


                              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

                                You are welcome