Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009

    Unanswered: Multi-Row update across 2 tables

    Hey all, I am having trouble with an update query.

    I am trying to do a multi-row update on a column on table1, going across table2, and setting it to a value on table3. I think I have the table1 and table3 values setup properly, but I think table2 is causing my issues.

    The error I'm receiving is "ORA-01427: single-row subquery returns more than one row."

    Here is the Update statement

    Update table1 tab1a
    set tab1.columnX = (select columnY from table1 tab1b
    join table2 tab2
    on tab2.table1_id = tab1b.table1_id
    join table3 tab3
    on tab3.table2_id = tab2.table2_id
    where tab1b.columnX < tab3.columnY
    and tab1a.table1_id = tab1b.table1_id)

    columnX is from table1
    columnY is from table3

    There should be 255 rows updated. If it helps, here is the select statement that gives me the result set I want.

    select tab1.table1_id, columnX, columnY from table1 tab1
    join table2 tab2
    on tab2.table1_id = tab1.table1_id
    join table3 tab3
    on tab3.table2_id = tab2.table2_id
    where tab1.columnX < tab3.columnY

    Any help provided is most appreciated, thanks.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >set tab1.columnX = (select columnY from table1 tab1b.......
    This syntax requires the SELECT return a single row because tab1.columnX can only hold 1 value.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Quote Originally Posted by Windowslm
    There should be 255 rows updated.
    Correct only if TABLE1 contains 255 rows. Because the UPDATE statement does not contain any WHERE condition, all its rows will be updated.
    For some examples, have a look at this thread:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts