5 Replies Latest reply: Mar 8, 2013 3:52 PM by Frank Kulash RSS

    Update Field1.Table1 based on compound join between Table1 and Table2

    980427
      Hi,

      I want to run update statement on Oracle 11g, but it fails with the following error:

      SQL Error: ORA-01427: single-row subquery returns more than one row
      01427. 00000 - "single-row subquery returns more than one row"

      Here's what I'm trying to do in pseudocode:

      Update Table1
      Set Flag_Column=1
      Where
      Table1.ColumnX=Table2.ColumnX
      and
      Table1.ColumnY=Table2.ColumunY

      Is there a way to do this in SQL?

      *****Try1*****
      update Table1
      set Flag_Column=1
      where ColumnX=
      (select distinct Table1.ColumnX
      from Table1, Table2
      where Table1.ColumnX=Table2.ColumnX
      and Table1.ColumnY=Table2.ColumnY)
      and ColumnY=
      (select distinct Table1.ColumnY
      from Table1, Table2
      where Table1.ColumnX=Table2.ColumnX
      and Table1.ColumnY=Table2.ColumnY)

      *****Try2*****
      update Table1
      set Flag_Column=
      (select 1
      from Table1, Table2
      where Table1.ColumnX=Table2.ColumnX
      and Table1.ColumnY=Table2.ColumnY)


      Any help is appreciated

      Edited by: 977424 on Mar 8, 2013 12:13 PM
        • 1. Re: Update Field1.Table1 based on compound join between Table1 and Table2
          Frank Kulash
          hi,

          Maybe:
          UPDATE     table1     t1
          SET     flag_column     = 1
          WHERE     EXISTS (
                          SELECT  1
                       FROM        table2
                       WHERE   columnX     = t1.columnX
                       AND        columnY     = t1.columnY
                      )
          ;
          Depending on your requirements, MERGE might be better than UPDATE.


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
          Explain, using specific examples, how you get those results from that data.
          See the forum FAQ {message:id=9360002}
          • 2. Re: Update Field1.Table1 based on compound join between Table1 and Table2
            user10857924
            why you join Table1 in the subquery with table2 you can use something as below...
            update Table1 
            set Flag_Column=1
            where EXISTS 
                 (
                      select null from table2
                      where Table1.ColumnX=Table2.ColumnX and Table1.ColumnY=Table2.ColumnY
                 )
            HTH...

            Thanks
            • 3. Re: Update Field1.Table1 based on compound join between Table1 and Table2
              980427
              This statement works, thank you. The only thing I did was add a distinct in order to get rid of the "single-row sub query..." error

              Hence, my real query is:

              UPDATE     mn003p     t1
              SET     DIM_PROCESS_FLAG     = 0
              WHERE     EXISTS (
                   SELECT distinct 1
                        FROM     dim_srl_price_file
                        WHERE item_number     = t1.item_number
                        AND     product_identifier     = t1.product_identifier
              )

              This works great.

              In what case would I use the MERGE statement? Do you know which is faster (MERGE vs UPDATE)?

              Thanks again!
              • 4. Re: Update Field1.Table1 based on compound join between Table1 and Table2
                980427
                This also works, thank you. The actual query is below and I did not have to add a distinct.

                update mn003p
                set DIM_PROCESS_FLAG=1
                where EXISTS
                     (
                          select null from dim_srl_price_file
                          where mn003p.item_number=dim_srl_price_file.item_number
                and mn003p.product_identifier=dim_srl_price_file.product_identifier
                     )

                Do you think this is faster then the previous query?

                Thanks as well!
                • 5. Re: Update Field1.Table1 based on compound join between Table1 and Table2
                  Frank Kulash
                  Hi,
                  977424 wrote:
                  This statement works, thank you. The only thing I did was add a distinct in order to get rid of the "single-row sub query..." error
                  DISTINCT isn't necessary; EXISTS sub-queries never raise the "... returns too many rows" error. In fact, EXISTS sub-queries stop as soon as they find a row. However, DISTINCT isn't doing any harm in this case.
                  Hence, my real query is:

                  UPDATE     mn003p     t1
                  SET     DIM_PROCESS_FLAG     = 0
                  WHERE     EXISTS (
                       SELECT distinct 1
                            FROM     dim_srl_price_file
                            WHERE item_number     = t1.item_number
                            AND     product_identifier     = t1.product_identifier
                  )

                  This works great.

                  In what case would I use the MERGE statement? Do you know which is faster (MERGE vs UPDATE)?
                  MERGE is a big improvement over UPDATE when the UPDATE statement has a sub-query in the SET clause, and then has a similar sub-query in the WHERE clause. Also, use MERGE instead of UPDATE when you need analytic functions.

                  I don't know of any good rules for guessing which is faster. In cases where I've seen a big difference, it was because UPDATE required redundant or complicated code, so I'd suggest that if MERGE looks tidier, then it's probably more efficient, too. Of course, in cases where efficiency is important, there's no substitute for actually testing both ways.
                  If there's an obvious, straightforward way to use UPDATE, I usually just do UPDATE. If performance is poor, then I'll look at MERGE as well.