12 Replies Latest reply on Jun 2, 2020 2:23 PM by mathguy

    Update Stored Procedure

    4243516

      HI All,

       

       

      I have a User Story requirement in my project wherein I need to update the PANEL CODE OF Networks based on NET ID=4. Please see example below:

       

      As of now:
      Header 1
      PRACPROVLOCNETPANEL CODESTART DATEEND DATE
      1002003003D32141/1/19001/1/4000
      1002003004D67891/1/19001/1/4000

       

       

      I need output as below:

       

      PRAC PROVLOCNET
      PANEL CODESTART DATEEND DATE
      1002003003D67891/1/19001/1/4000
      1002003004D67891/1/19001/1/4000

       

       

      Please give me some tips how to make these changes and achieve these results. I need to update the stored procedure.

       

      Thanks.

        • 1. Re: Update Stored Procedure
          Paulzip

          Please give table creation script and insert statements.

           

          So you want to update all other PANEL CODE values based on the PANEL CODE for NET = 4?

          • 2. Re: Update Stored Procedure
            mathguy

            So, you have a table, and it has several columns, including NET and PANEL CODE. (PANEL CODE has an embedded space; I hope that is not the real column name, or is it?)

             

            You must find the PANEL CODE for NET = 4 and change the PANEL CODE in other rows to this PANEL CODE (found in the row with NET = 4).

             

            Questions:

             

            (1)  Which rows should be changed?  All the rows in the table?  Only the rows with NET = 3? Or some other subset of rows, which you did not fully describe in your question?

             

            (2) Will there always be exactly one row with NET = 4?  What if there is no such row? What if there is more than one?

             

            (3) Suppose there is exactly one row with NET = 4. Can PANEL CODE be NULL - and if it is, do you want to update other values to NULL?

            • 3. Re: Update Stored Procedure
              4243516

              HI,

               

              Thanks for your reply. Below are my inputs:

               

              (1)  Which rows should be changed?  All the rows in the table?  Only the rows with NET = 3? Or some other subset of rows, which you did not fully describe in your question?

               

              Yes all the rows in table should be changed.

               

              (2) Will there always be exactly one row with NET = 4?  What if there is no such row? What if there is more than one?

               

              There can be n number of rows with NET =4 and if not such row no action needs to be taken.

               

               

              (3)  Suppose there is exactly one row with NET = 4. Can PANEL CODE be NULL - and if it is, do you want to update other values to NULL?

               

              No panel code should not be NULL.

              • 4. Re: Update Stored Procedure
                mathguy

                So, there is still one question you need to answer. If there are n rows with NET = 4, and n > 1, which PANEL CODE should be used? There are n rows to choose from; do they all have the same PANEL CODE in all cases? If not, you need to give us more rules, to know which PANEL CODE to use (from the n rows where NET = 4).

                • 5. Re: Update Stored Procedure
                  Stefan Jager

                  Based on the very small samples you give,

                   

                  update <table> set "panel code" = 'D6789'

                   

                  Seems to do the trick. It doesn't make sense, but if absolutely necessary you could add

                   

                  where net<>4

                   

                  But effectively the end result without the where clause will be the same.

                  • 6. Re: Update Stored Procedure
                    4243516

                    HI Stefan,

                     

                    Thanks for reply. I want the PANEL_CODE to not be hard-corded, I want it should automatically set it to the panel_code of NET=4 for other net ids.

                    • 7. Re: Update Stored Procedure
                      Stefan Jager

                      4243516 wrote:

                       

                      HI Stefan,

                       

                      Thanks for reply. I want the PANEL_CODE to not be hard-corded, I want it should automatically set it to the panel_code of NET=4 for other net ids.

                      Then instead of giving us information piece by piece,  describe your tables, your code and your requirement in full? I based my answer on the information in your original post...

                       

                      Re: 2. How do I ask a question on the forums?

                      • 8. Re: Update Stored Procedure
                        odie_63

                        4243516 wrote:

                         

                        HI Stefan,

                         

                        Thanks for reply. I want the PANEL_CODE to not be hard-corded, I want it should automatically set it to the panel_code of NET=4 for other net ids.

                        You still don't answer mathguy's question in reply #4.

                        • 9. Re: Update Stored Procedure
                          4243516

                          Yes mathew the Panel code will be same for net=4 for all rows and  that needs to be assigned to all other net ID'S Panel codes.

                          • 10. Re: Update Stored Procedure
                            4243516

                            Sorry for that actually the code was really wrong so I just placed the information in form of short examples to tell the issue being faced.

                            • 11. Re: Update Stored Procedure
                              mathguy

                              I think this should work. I assumed the "panel code" column name has an underscore; if it doesn't, modify as needed.

                               

                              update  <table>

                                set   panel_code = (select panel_code from <table> where net = 4 and rownum = 1)

                                where exists (select * from <table> where net = 4)

                                  and net != 4

                              ;

                              • 12. Re: Update Stored Procedure
                                mathguy

                                Actually MERGE may work better in this case:

                                 

                                merge   into <table> t

                                  using (select panel_code from <table> where net = 4 and rownum = 1) s

                                  on    (t.net != 4)

                                when matched then update

                                  set   t.panel_code = s.panel_code

                                ;

                                 

                                Nothing is updated when NET is never equal to 4; if NET is 4 in at least one row, the only "matched" rows in the target are those where NET != 4, so only those rows will be updated.