1 2 Previous Next 18 Replies Latest reply: Oct 15, 2012 2:02 PM by user9026725 RSS

    update select into query

    user9026725
      I have two tables that and I'm trying to update a field on one table with the data from a field in another table. Im a getting error with single row subquery returns more than one row. Table A has more data in it than table B but they have the same amount with the id's matching. This is my query that I'm trying to run:

      update tableA
      set A.ed = (select substr(b.usr_ed, 0, 10)
      From table b
      where A.ed_id = B.ed_id)
        • 1. Re: update select into query
          Solomon Yakobson
          It means there are multiple matching rows in table B for at least one A.ed_id. Therefore Oracle doesn't know which one to use. If you don't care which of matching table B rows to use:
          update tableA
          set A.ed = (select substr(b.usr_ed, 0, 10)
          From table b
          where A.ed_id = B.ed_id and rownum = 1) 
          SY.
          P.S. Keep in mind, your query will update A.ed to NULL for all table A rows where there is no matching table B rows.
          • 2. Re: update select into query
            user9026725
            Thank you for your help. I added that and now i'm getting the error invalid number under the Substr(usr_system_username,0,10)?
            • 3. Re: update select into query
              rp0428
              >
              I added that and now i'm getting the error invalid number under the Substr(usr_system_username,0,10)?
              >
              And what would you expect Oracle to do if you tell it to put 'abc' into a NUMBER column?

              You didn't post the table DDL but you must be trying to put the value into a numeric column.

              And the first 10 characters of 'usr_system_username' are not a number for at least one record.

              You need to find the invalid data and clean it up or modify the substr so it doesn't select non-numeric characters.
              • 4. Re: update select into query
                Nimish Garg
                try as below
                update tableA
                set A.ed = (select substr(b.usr_ed, 0, 10)
                From table b
                where A.ed_id = B.ed_id and rownum = 1 and ISNUMERIC(substr(b.usr_ed, 0, 10)) = 1)
                ISNUMERIC is a pl/sql function and its definition is here
                http://nimishgarg.blogspot.in/2010/03/oracle-function-isnumeric-check-value.html
                • 5. Re: update select into query
                  Chanchal Wankhade
                  Hi,

                  Try something like this if you want to update all the records.
                  declare
                  cursor cur is select ed_id from table_B;
                  begin
                  for rec in cur loop
                  update table_A set a.ed_id=rec.ed_id
                  where a.ed_id=rec.ed_id;
                  end; 
                  • 6. Re: update select into query
                    jeneesh
                    Chanchal Wankhade wrote:
                    Hi,

                    Try something like this if you want to update all the records.
                    declare
                    cursor cur is select ed_id from table_B;
                    begin
                    for rec in cur loop
                    update table_A set a.ed_id=rec.ed_id
                    where a.ed_id=rec.ed_id;
                    end; 
                    Cusor will be a bad idea here, since the samething can be done in single SQL....
                    • 7. Re: update select into query
                      Chanchal Wankhade
                      yes cursor may be bad but he can use it if he does'n required it regulary..
                      declare
                      cursor cur is select ed_id from table_B;
                      begin
                      for rec in cur loop
                      select count(*) into ed_v from table_A where ed_id=rec.ed_id;
                      if counter >0 then
                      update table_A set a.ed_id=rec.ed_id
                      where a.ed_id=rec.ed_id;
                      else
                      insert into table_A values(_________);
                      end if;
                      end loop;
                      
                      end;
                      • 8. Re: update select into query
                        Chanchal Wankhade
                        yes cursor may be bad but he can use it if he does'n required it regulary..
                        declare
                        counter number;
                        cursor cur is select ed_id from table_B;
                        begin
                        for rec in cur loop
                        select count(*) into ed_v from table_A where ed_id=rec.ed_id;
                        if counter >0 then
                        update table_A set a.ed_id=rec.ed_id
                        where a.ed_id=rec.ed_id;
                        else
                        insert into table_A values(_________);
                        end if;
                        end loop;
                        
                        end;
                        • 9. Re: update select into query
                          jeneesh
                          Chanchal Wankhade wrote:
                          yes cursor may be bad but he can use it if he does'n required it regulary..
                          If it is a bad idea, why are you suggesting it?

                          I cant see areason to use cursor...

                          What ever you can trying to do in the above post can be done using a MERGE....

                          Dont go for PL/SQL if it is poossible using SQL - Never...

                          Edited by: jeneesh on Oct 11, 2012 12:10 PM
                          • 10. Re: update select into query
                            user9026725
                            Ok, one thing I should have mentioned is that the first 10 are number and the 11 is a letter that I want to drop from my field.
                            • 11. Re: update select into query
                              Sven W.
                              user9026725 wrote:
                              Ok, one thing I should have mentioned is that the first 10 are number and the 11 is a letter that I want to drop from my field.
                              It seems as if you have some rows where the 10 is also a letter.

                              Does you data look like this?
                              1234567890A
                              1234567890B
                              2345678901A
                              3456789012A
                              4567890123A
                              1234567890C
                              1234567890D
                              0123456789E
                               123456789E
                              The last entry would raise your error.
                              • 12. Re: update select into query
                                user9026725
                                Yes
                                • 13. Re: update select into query
                                  Sven W.
                                  Instead of substr you can use a regular expression. For example the following would find up to 10 digits from your string.
                                  regexp_substr(yourColumn,'^\d{0,10}') 
                                  it will stop searching, as soon as a non digit character was found (like a letter or a blank).

                                  Edited by: Sven W. on Oct 11, 2012 7:50 PM
                                  • 14. Re: update select into query
                                    user9026725
                                    The last suggestion updated all fields with 0. Is there another way to tell it you just want to drop the letter off the end?

                                    I'm not sure if I am making clear what I am trying to do. A have a field that is Varchar 11 and one field that is number 10. I want to update the number field with the data from the Varchar field but I don't want the char on the end of the string to come over. Is there an easier way then what I'm trying to do.

                                    Edited by: user9026725 on Oct 11, 2012 11:27 AM
                                    1 2 Previous Next