This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Oct 15, 2012 12:02 PM by user9026725 RSS

update select into query

user9026725 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yes
  • 13. Re: update select into query
    Sven W. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points