11 Replies Latest reply on Feb 10, 2014 9:43 AM by 76425c46-a19e-4509-b28d-b5c0fbb0b433

    Need Help

    76425c46-a19e-4509-b28d-b5c0fbb0b433

      Hi, I need help in creating this PLSQL

       

      I have two tables, Table "A" and Table "B"

      Table A

      ----------

      EQP_ID  ALARM_ID  ENABLE_FLAG

      IHD-501     dd                    Y

      IHD-503      ff                     N

      IHD-504      gg                   Y

       

      Table B

      ----------

      EQP_ID  ALARM_ID  ENABLE_FLAG

      IHD-501     xx                    Y

      IHD-503     yy                    N

      IHD-504      rr                    N

       

      Table C

      ----------

      EQP_ID          FAB

      IHD-501               1

      IHD-503              2

      IHD-504              2

       

      I have to update table A based on Table C. If in Table C the corresponding EQP_ID belongs to FAB "2", then I have to take the record from Table B and update it into Table A. If in Table C the corresponding EQP_ID belongs to FAB "1" then I just have to retain that record in TableA

      So the final table should be:

      Table A

      ----------

      EQP_ID  ALARM_ID  ENABLE_FLAG

      IHD-501     dd                    Y

      IHD-503     yy                    N

      IHD-504      rr                    N

       

      What will be the PLSQL query for this? A procedure with the table name as parameters (A,B,C) would be good as I will have to pass various such combination of tables

        • 1. Re: Need Help
          Brian Bontrager

          You can meet the requirement without any PL/SQL at all.

           

          update table_a

              set (alarm_id,enable_flag)=(select alarm_id,enable_flag from table_b where table_a.eqp_id=table_b.eqp_id)

          where exists (select 1 from table_c where table_a.eqp_id=table_c.eqp_id and table_c.fab=2);

           

           

          What should be done if there is no row in TABLE_C?

           

           

          You say you need to do this for multiple combinations of tables (starts to sound like a Dynamic SQL need, so PL/SQL comes back into consideration). How many tables are you talking about?  Are they always the same?  Are the table structures always the same?

          • 2. Re: Need Help
            Partha Sarathy S

            UPDATE TABLEA A SET (ALARM_ID,ENABLE_FLAG) =

                                                        (SELECT ALARM_ID,ENABLE_FLAG FROM TABLEB

                                                        WHERE A.EQP_ID=EQP_ID)

                WHERE EXISTS (SELECT 1 FROM TABLEC

                               WHERE TABLEC.EQP_ID=A.EQP_ID AND TABLEC.FAB=2);

            • 3. Re: Need Help
              Paul  Horth

              Are all the table structures the same? If so, this looks like bad design: you shouldn't really be passing in table names as parameters - that implies dynamic SQL.

              If not, then it sounds even crazier!

               

              Anyway, you don't need PL/SQL:

               

              merge into tablea a

              using (

              select b.eqp_id, b.alarm_id, b.enable_flag

              from tableb b

              inner join tablec c

              on c.eqp_id = b.eqp_id where c.fab=2) t

              on (a.eqp_id = t.eqp_id)

              when matched then

                update

                set a.alarm_id = t.alarm_id,

                a.enable_flag = t.enable_flag

                ;

              • 4. Re: Need Help
                AnnEdmund

                Try the below.. You can change the table name and run this update

                UPDATE tablea a SET (alarm_id,enable_flag) = (SELECT b.alarm_id,b.enable_flag

                                                              FROM table b

                                                              WHERE a.EQP_ID = b.EQP_ID)

                WHERE EXISTS (SELECT 1

                              FROM tablec c

                              WHERE a.EQP_ID = c.EQP_ID

                              AND c.fab = 2);

                 

                Message was edited by: 000000

                • 5. Re: Need Help
                  76425c46-a19e-4509-b28d-b5c0fbb0b433

                  Thanks Brian for your response! If there are no corresponding rows in Table C, then we do not have to do anything with that record in Table A, let the record remain in Table A.

                  But my concern is the number of columns in table A and Table B may not just be two columns, it can be even about 15 columns and I did not want to specify every column in the query. And also the fact that for the other sets, the table structures will not be the same as this set which I have listed. The tables which I have shows in this example is just simple.

                  Totally I have about 20 such sets

                   

                  Please advise!

                  • 6. Re: Need Help
                    Partha Sarathy S

                    If you have other columns, as you said upto 15 columns, those values will be in tableA and tableB right.? So if the condition you specified for joining table can uniquely identify a row, then update should be a problem for whatever number of columns it is.

                    • 7. Re: Need Help
                      Brian Bontrager

                      If the table structures are not the same, PL/SQL will make it unnecessarily difficult.  You would likely write MORE code than simply listing the specific columns you need in SQL.  The testing, debugging, and maintenance of a PL/SQL equivalent will far outweigh the time you invest to write the SQL in this case; not to mention you may end up with a solution that is slower. 15 columns is not a lot, even over 20 tables, since much of the work will be copy-paste.

                       

                      To save a little typing, I often use this to get a quick dump of the columns in a table (with commas, ready to copy-paste into a query) if I need to use all or most columns in a query.

                       

                      select column_name||',' col from dba_tab_columns
                      where owner='MYSCHEMA' and table_name='MYTABLE'
                      order by column_id;
                      
                      1 person found this helpful
                      • 8. Re: Need Help
                        76425c46-a19e-4509-b28d-b5c0fbb0b433

                        Hi Brian, So you mean to say the below should work:

                        Update table_a

                        set (alarm_id,enable_flag,......)=(select alarm_id,enable_flag,..... from table_b where table_a.eqp_id=table_b.eqp_id)

                        where exists (select 1 from table_c where table_a.eqp_id=table_c.eqp_id and table_c.fab=2);

                        • 10. Re: Need Help
                          AnnEdmund

                          Is it working now?