9 Replies Latest reply: Dec 7, 2012 4:45 AM by 21205 RSS

    Update statement

    884519
      Hello All,

      Can someone please find the problem with below script?
      I am getting error.

      update gl_iea_subsidiaries gis
      set gis.attribute2 =
      (select hoi.organization_id
      from hr_organization_information hoi, gl_iea_subsidiaries gis1
      where to_char(gis1.set_of_books_id) = hoi.org_information3) abc
      where to_char(gis.set_of_books_id) = abc.org_information3
      and gis.set_of_books_id not in (35, 4, 9, 1400, 5)
      and xy.organization_id not in (35, 4, 9, 1400, 5)


      Thanks,
      gvk.
        • 1. Re: Update statement
          21205
          The error points to where the error originates from:
          SQL> update gl_iea_subsidiaries gis
            2  set gis.attribute2 =
            3  (select hoi.organization_id
            4  from hr_organization_information hoi, gl_iea_subsidiaries gis1
            5  where to_char(gis1.set_of_books_id) = hoi.org_information3) abc
            6  where to_char(gis.set_of_books_id) = abc.org_information3
            7  and gis.set_of_books_id not in (35, 4, 9, 1400, 5)
            8  and xy.organization_id not in (35, 4, 9, 1400, 5)update gl_iea_subsidiaries gis
            9  set gis.attribute2 =
           10  (select hoi.organization_id
           11  from hr_organization_information hoi, gl_iea_subsidiaries gis1
           12  where to_char(gis1.set_of_books_id) = hoi.org_information3) abc
           13  where to_char(gis.set_of_books_id) = abc.org_information3
           14  and gis.set_of_books_id not in (35, 4, 9, 1400, 5)
           15  and xy.organization_id not in (35, 4, 9, 1400, 5)
           16  /
          where to_char(gis1.set_of_books_id) = hoi.org_information3) abc
                                                                      *
          ERROR at line 5:
          ORA-00933: SQL command not properly ended
          • 2. Re: Update statement
            21205
            and there are two update statement which are not separated by an ";"
            • 3. Re: Update statement
              vijayrsehgal-Oracle
              sure we can, but if you put error message to help us. Hopefullly below should be fine, can't test as I don't have your tables. Also this will update records in gl_iea_subsidiaries for matching records found and for the records there's no match field attribute2 will be set to null, is that what you intend?
              UPDATE gl_iea_subsidiaries gis
                 SET gis.attribute2 =
                        (SELECT hoi.organization_id
                           FROM hr_organization_information hoi, gl_iea_subsidiaries gis1
                          WHERE TO_CHAR (gis1.set_of_books_id) = hoi.org_information3
                            AND TO_CHAR (gis.set_of_books_id) = abc.org_information3
                            AND gis.set_of_books_id NOT IN (35, 4, 9, 1400, 5)
                            AND xy.organization_id NOT IN (35, 4, 9, 1400, 5)
              )
              • 4. Re: Update statement
                884519
                Thanks for the reply but it's still throwing error.
                • 5. Re: Update statement
                  vijayrsehgal-Oracle
                  Post the error message and also your table structures so that someone can test, just saying it throws error people won't be able to help you in the best possible way.
                  • 6. Re: Update statement
                    lee200
                    The problem is with this line:
                    AND TO_CHAR (gis.set_of_books_id) = abc.org_information3
                    The alias 'abc' does not exist
                    • 7. Re: Update statement
                      Ora_20
                      Can you try this Statment :

                      UPDATE (
                      select hoi.organization_id, gis.attribute2
                      from hr_organization_information hoi, gl_iea_subsidiaries gis1
                      where to_char(gis1.set_of_books_id) = hoi.org_information3
                      and gis.set_of_books_id not in (35, 4, 9, 1400, 5)
                      and xy.organization_id not in (35, 4, 9, 1400, 5)
                      )
                      SET gis.attribute2 = hoi.organization_id
                      where gis.set_of_books_id not in (35, 4, 9, 1400, 5);



                      Regards
                      • 8. Re: Update statement
                        884519
                        I am trying the below simple statement:

                        update gl_iea_subsidiaries gis
                        set gis.attribute2 =
                        (select hoi.organization_id
                        from hr_organization_information hoi, gl_iea_subsidiaries gis1
                        where to_char(gis1.set_of_books_id) = hoi.org_information3) abc
                        where to_char(gis.set_of_books_id) = abc.org_information3

                        Error I am getting is :
                        ORA-00933: SQL command not properly ended
                        • 9. Re: Update statement
                          21205
                          remove the "abc"...
                          update gl_iea_subsidiaries gis 
                          set gis.attribute2 = 
                          (select hoi.organization_id 
                          from hr_organization_information hoi, gl_iea_subsidiaries gis1 
                          where to_char(gis1.set_of_books_id) = hoi.org_information3)
                          where to_char(gis.set_of_books_id) = abc.org_information3