1 2 Previous Next 21 Replies Latest reply: Feb 29, 2012 1:32 AM by 901702 RSS

    single-row subquery returns more than one row

    759148
      this is my sql:

      UPDATE TEMP_SELLOUT_MP_HY SET ID = (SELECT ID FROM REF_TOKO WHERE NAMA IN(SELECT NAMA_TOKO FROM TEMP_SELLOUT_MP_HY WHERE KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAIMALL')) WHERE KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAIMALL'


      Error at line 1
      ORA-01427: single-row subquery returns more than one row


      Please tell me how i fix it this my query. Thanks...
        • 1. Re: single-row subquery returns more than one row
          009
          Hi,

          Check how many rows does your SQL return, you can not update more than 1 value in a cell :)
          SELECT ID
            FROM ref_toko
           WHERE nama IN (SELECT nama_toko
                            FROM temp_sellout_mp_hy
                           WHERE kota = 'YOGYAKARTA6' AND nama_mall = 'RAMAIMALL')
          *009*

          Edited by: 009 on Mar 10, 2010 2:03 AM
          • 2. Re: single-row subquery returns more than one row
            21205
            Welcome to the forum!


            It would seem that this query
            (select id
                           from ref_toko
                          where nama in (select nama_toko
                                           from temp_sellout_mp_hy
                                          where kota = 'YOGYAKARTA6'
                                            and nama_mall = 'RAMAIMALL'))
            returns more than one row...
            • 3. Re: single-row subquery returns more than one row
              728534
              Hi,
              This may resolve your issue
              UPDATE TEMP_SELLOUT_MP_HY SET ID = (SELECT distinct ID FROM REF_TOKO WHERE NAMA IN(SELECT NAMA_TOKO FROM TEMP_SELLOUT_MP_HY WHERE KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAIMALL')) WHERE KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAIMALL'
              Cheers!!!
              Bhushan
              • 4. Re: single-row subquery returns more than one row
                759148
                thanks for your answer..

                but it still error..please any solusition. Thanks
                • 5. Re: single-row subquery returns more than one row
                  009
                  If you want incorrect solution then
                  UPDATE temp_sellout_mp_hy
                     SET ID =
                            (SELECT ID
                               FROM ref_toko
                              WHERE nama IN (
                                          SELECT nama_toko
                                            FROM temp_sellout_mp_hy
                                           WHERE kota = 'YOGYAKARTA6'
                                                 AND nama_mall = 'RAMAIMALL')
                                         and rownum<=1)
                   WHERE kota = 'YOGYAKARTA6' AND nama_mall = 'RAMAIMALL';
                  But its not a correct SQL, its corrected SQL. Find out whats wrong with your SQL

                  *009*
                  • 6. Re: single-row subquery returns more than one row
                    Satyaki_De
                    Something like this ->
                    UPDATE TEMP_SELLOUT_MP_HY e
                    SET e.ID = (
                                 SELECT k.ID 
                                 FROM REF_TOKO k
                                 WHERE k.NAMA IN (
                                                  SELECT NAMA_TOKO 
                                                  FROM TEMP_SELLOUT_MP_HY 
                                                  WHERE KOTA = 'YOGYAKARTA6' 
                                                  AND NAMA_MALL = 'RAMAIMALL'
                                                 )
                                 AND k.ID = e.ID
                              ) 
                    WHERE KOTA = 'YOGYAKARTA6' 
                    AND NAMA_MALL = 'RAMAIMALL';
                    N.B.: Not Tested.....

                    Regards.

                    Satyaki De.
                    • 7. Re: single-row subquery returns more than one row
                      009
                      Hi Satyaki,
                      AND k.ID = e.ID
                      A doubt..... if k.ID = e.ID then y does a developer need to update it?

                      *009*
                      • 8. Re: single-row subquery returns more than one row
                        Satyaki_De
                        Oops!

                        Good Point. ;)

                        May be something related to this have to introduced here - only then it will remove that multiple rows and return seingle row.

                        Let me check that again.

                        Regards.

                        Satyaki De.
                        • 9. Re: single-row subquery returns more than one row
                          728534
                          hi,
                          Really interested in teh Corrected SQL? :)

                          OP,
                          try this ;)
                          UPDATE TEMP_SELLOUT_MP_HY
                             SET ID = (SELECT max(ID)
                                         FROM REF_TOKO
                                        WHERE NAMA IN (SELECT NAMA_TOKO
                                                         FROM TEMP_SELLOUT_MP_HY
                                                        WHERE KOTA = 'YOGYAKARTA6'
                                                          AND NAMA_MALL = 'RAMAIMALL'))
                           WHERE KOTA = 'YOGYAKARTA6'
                             AND NAMA_MALL = 'RAMAIMALL'
                          NOTE the id that it updates will be the MAX(id) for whatever join conditiion you have
                          After updating there is a very very high possibility you will start another thread saying Updated values not correct.
                          So check your data..Check what you want to update as what.
                          Post sample queries,sample data (Create table/insert statements) and see the Gurus pitching in their Ideas.

                          Cheers!!!
                          Bhushan
                          • 10. Re: single-row subquery returns more than one row
                            759148
                            thanks for all your replies.

                            my table ref_toko is:

                            ID
                            -------------------------
                            1214004115
                            1701001095
                            1501005011
                            1501001028
                            1501001029
                            1501001030
                            1501001031
                            1501001032
                            1501001033
                            1501001034
                            1501001035
                            1501001036
                            1501001044
                            1501001072
                            1501002026
                            1501001026
                            1501001027
                            1501001059
                            1501001060
                            1501001061
                            1501001062

                            ID
                            -------------------------
                            1501001063
                            1501001071
                            1501001074
                            1501002040
                            1501001037
                            1501001038
                            1501001042
                            1501001045
                            1501001046
                            1501001047
                            1501001048
                            1501001049
                            1501001051
                            1501001052
                            1501001053
                            1501001054
                            1501001055
                            1501001056
                            1214004019
                            1501001064
                            1501001065

                            ID
                            -------------------------
                            1501001067
                            1501001068
                            1501001069
                            1501001070
                            1501001073
                            1501001001
                            1501001002
                            1501001003
                            1501001005
                            1501001006
                            1501001007
                            1501001008
                            1501001012
                            1501001039
                            1501001040
                            1501001043
                            1501001058
                            1202004052
                            1501001009
                            1501001010
                            1501001011

                            ID
                            -------------------------
                            1501001013
                            1501001014
                            1501001015
                            1501001016
                            1501001017
                            1501001018
                            1501001019
                            1501001020
                            1501001021
                            1501001022
                            1501001023
                            1501001024
                            1501001025
                            1501001041
                            1501001057
                            1701001010
                            1401001033
                            1501001004
                            1501001050


                            i want to update my id in table TEMP_SELLOUT_MP_HY where KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAI MALL'


                            thanks..
                            • 11. Re: single-row subquery returns more than one row
                              728534
                              Hey,
                              I said Sample data :)
                              Anyway, try this query, not sure why you refer the same table twice, i dont think its necessary (Or i am hungry and need to have lunch :) )
                              UPDATE TEMP_SELLOUT_MP_HY x
                                 SET ID = (SELECT distinct ID
                                             FROM REF_TOKO y
                                            WHERE y.NAMA =x.NAMA_TOKO
                                            )
                               WHERE KOTA = 'YOGYAKARTA6'
                                 AND NAMA_MALL = 'RAMAIMALL'
                                 and exists (
                                 SELECT distinct ID
                                             FROM REF_TOKO y
                                            WHERE y.NAMA =x.NAMA_TOKO
                                 
                                 )
                              Cheers!!!
                              Bhushan
                              • 12. Re: single-row subquery returns more than one row
                                759148
                                this is my sample data

                                i have two table is
                                table ref_toko and temp_sellout_mp_hy..

                                in my table ref_toko is id and nama_toko, example:

                                ID NAMA
                                ------------------------- --------------------------------------------------
                                1214004115 OWEN CELL
                                1701001095 KENCANA CELL
                                1501005011 LORDS CELL
                                1501001028 PRODYA CELL
                                1501001029 DEVA JAYA CELL
                                1501001030 MILLION CELL
                                1501001031 AMORPHONE
                                1501001032 R-CELL
                                1501001033 MARCHEL
                                1501001034 LINK PHONE
                                1501001035 PONSEL 99
                                1501001036 VINZO
                                1501001044 NENG PHONE CELL
                                1501001072 AMOR PHONE
                                1501002026 KING CELLULAR
                                1501001026 MR PHONE CELLULAR
                                1501001027 RAMAI PHONE
                                1501001059 PROXY
                                1501001060 8-STAR PHONE SHOP
                                1501001061 EMERALD PHONE
                                1501001062 ELECTRIC-CELL

                                in my table temp_sellout_mp_hy is nama_toko, example:

                                NAMA_TOKO
                                --------------------------------------------------
                                OCTOPUS CELL
                                METRO PHONE SHOP
                                ABADI PHONE
                                D-PHONE CELL
                                PLANET MOBILE PHONE SHOP
                                SSS (SELULER SELULER SELULER)
                                FLASH PHONE
                                SPEED CELL
                                OWEN CELLULAR CERVICE
                                EMPIRE
                                OWEN CELL
                                ORANGE PHONE
                                DIAMOND
                                HANSD PHONE
                                EFATA CELLULER
                                LAREES PHONE
                                XCELINDO
                                CANARY CELL
                                W@P SELULAR
                                NENG PHONE CELL
                                ZEUS MULTIMEDIA

                                NAMA_TOKO
                                --------------------------------------------------
                                SHAQUILA PHONE HOUSE
                                TIARA CELLULAR
                                ONE BY ONE
                                LIONTIN CELULLAR
                                ASIA PHONEZONE
                                KENCANA CELL
                                X-TREME CELLULAR
                                CIKAL SATYA
                                ANDITA CELL
                                MU
                                VISION JAYA CELL
                                ZEUS MULTIMEDIA
                                IRIS PHONE
                                CAHAYA MUTIARA CELLULAR
                                NEO PHONE
                                ALDILLA
                                VIA SELL
                                INTER SELL
                                FAMILY PHONE
                                ELECTRIC-CELL
                                ELECTRIC-CELL

                                NAMA_TOKO
                                --------------------------------------------------
                                ONE & ONE
                                SATRIA 87 CELLULAR
                                PACIFIC SELLULER
                                MEGAVON
                                PROXY
                                8-STAR PHONE SHOP
                                EMERALD PHONE
                                OCTOPUS CELL
                                METRO PHONE SHOP
                                ABADI PHONE
                                D-PHONE CELL
                                PLANET MOBILE PHONE SHOP
                                SSS (SELULER SELULER SELULER)
                                FLASH PHONE
                                SPEED CELL
                                OWEN CELLULAR CERVICE
                                EMPIRE
                                OWEN CELL
                                ORANGE PHONE
                                DIAMOND
                                HANSD PHONE

                                NAMA_TOKO
                                --------------------------------------------------
                                EFATA CELLULER
                                LAREES PHONE
                                XCELINDO
                                CANARY CELL
                                W@P SELULAR
                                NENG PHONE CELL
                                ZEUS MULTIMEDIA
                                SHAQUILA PHONE HOUSE
                                TIARA CELLULAR
                                ONE BY ONE
                                LIONTIN CELULLAR
                                ELECTRIC-CELL
                                MACKINDO JAYA
                                LORDS CELL
                                INNOVA
                                CONCEPT COMUNICATION
                                RAIHAN SELLULAR
                                KING CELLULAR
                                PRODYA CELL
                                SHAQUILA PHONE HOUSE
                                AMOR PHONE

                                NAMA_TOKO
                                --------------------------------------------------
                                HIFONE
                                HANSD PHONE
                                MR PHONE CELLULAR
                                RAMAI PHONE
                                PRODYA CELL
                                DEVA JAYA CELL
                                MILLION CELL
                                AMORPHONE
                                R-CELL
                                MARCHEL
                                LINK PHONE
                                PONSEL 99
                                VINZO
                                ANUGRAH KOMUNIKA

                                i want to get id value from ref_toko into temp_sellout_mp_hy where nama_toko in ref_toko same in nama_toko in temp_sellout_mp_hy.

                                this is my code:

                                UPDATE TEMP_SELLOUT_MP_HY SET ID =
                                (SELECT ID, NAMA FROM REF_TOKO WHERE NAMA IN(SELECT NAMA_TOKO FROM TEMP_SELLOUT_MP_HY WHERE KOTA = 'YOGYAKARTA6'
                                AND NAMA_MALL = 'RAMAI MALL'))
                                WHERE KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAI MALL'

                                Thanks

                                Edited by: user12558956 on Mar 10, 2010 5:16 AM
                                • 13. Re: single-row subquery returns more than one row
                                  752245
                                  Mate,

                                  First of all let us know what the below query returns:
                                  SELECT ID FROM REF_TOKO WHERE NAMA IN(
                                  SELECT NAMA_TOKO FROM TEMP_SELLOUT_MP_HY WHERE KOTA = 'YOGYAKARTA6' AND NAMA_MALL = 'RAMAIMALL')
                                  I am very sure its returning more than one row.And if thats the case, there is no problem with the update - problem is in the subquery and needs to be changed to return only one row
                                  • 14. Re: single-row subquery returns more than one row
                                    728534
                                    Hi,
                                    Please dont mind but youa re making only things difficult.
                                    1) you did not post create/insert statements.
                                    2) the sample data you posted is not complete
                                    3) You did not even care to check the query given earlier.

                                    the problem you have asked is one of very very basic problems which the guys in the forun can answer within minutes, but you had to wait for hours (i guess).
                                    I have done my best to recreate your scenario and help you to the best level.
                                    Here you go
                                    create table ref_toko as(
                                    select '1214004115' id, 'OWEN CELL' nama         from dual union all
                                    select '1701001095', 'KENCANA CELL'      from dual union all
                                    select '1501005011', 'LORDS CELL'        from dual union all
                                    select '1501001028', 'PRODYA CELL'       from dual union all
                                    select '1501001029', 'DEVA JAYA CELL'    from dual union all
                                    select '1501001030', 'MILLION CELL'      from dual union all
                                    select '1501001031', 'AMORPHONE'         from dual union all
                                    select '1501001032', 'R-CELL'            from dual union all
                                    select '1501001033', 'MARCHEL'           from dual union all
                                    select '1501001034', 'LINK PHONE'        from dual union all
                                    select '1501001035', 'PONSEL 99'         from dual union all
                                    select '1501001036', 'VINZO'             from dual union all
                                    select '1501001044', 'NENG PHONE CELL'   from dual union all
                                    select '1501001072', 'AMOR PHONE'        from dual union all
                                    select '1501002026', 'KING CELLULAR'     from dual union all
                                    select '1501001026', 'MR PHONE CELLULAR' from dual union all
                                    select '1501001027', 'RAMAI PHONE'       from dual union all
                                    select '1501001059', 'PROXY'             from dual union all
                                    select '1501001060', '8-STAR PHONE SHOP' from dual union all
                                    select '1501001061', 'EMERALD PHONE'     from dual union all
                                    select '1501001062', 'ELECTRIC-CELL'     from dual)
                                    
                                    
                                    create table temp_sellout_mp_hy as(
                                    select 'OCTOPUS CELL' nama_toko                       from dual union all
                                    select 'METRO PHONE SHOP'                   from dual union all
                                    select 'ABADI PHONE'                        from dual union all
                                    select 'D-PHONE CELL'                       from dual union all
                                    select 'PLANET MOBILE PHONE SHOP'           from dual union all
                                    select 'SSS (SELULER SELULER SELULER)'      from dual union all
                                    select 'FLASH PHONE'                        from dual union all
                                    select 'SPEED CELL'                         from dual union all
                                    select 'OWEN CELLULAR CERVICE'              from dual union all
                                    select 'EMPIRE'                             from dual union all
                                    select 'OWEN CELL'                          from dual union all
                                    select 'ORANGE PHONE'                       from dual union all
                                    select 'DIAMOND'                            from dual union all
                                    select 'HANSD PHONE'                        from dual union all
                                    select 'EFATA CELLULER'                     from dual union all
                                    select 'LAREES PHONE'                       from dual union all
                                    select 'XCELINDO'                           from dual union all
                                    select 'CANARY CELL'                        from dual union all
                                    select 'W@P SELULAR'                        from dual union all
                                    select 'NENG PHONE CELL'                    from dual union all
                                    select 'ZEUS MULTIMEDIA'                    from dual union all
                                    select 'SHAQUILA PHONE HOUSE'               from dual union all
                                    select 'TIARA CELLULAR'                     from dual union all
                                    select 'ONE BY ONE'                         from dual union all
                                    select 'LIONTIN CELULLAR'                   from dual union all
                                    select 'ASIA PHONEZONE'                     from dual union all
                                    select 'KENCANA CELL'                       from dual union all
                                    select 'X-TREME CELLULAR'                   from dual union all
                                    select 'CIKAL SATYA'                        from dual union all
                                    select 'ANDITA CELL'                        from dual union all
                                    select 'MU'                                 from dual union all
                                    select 'VISION JAYA CELL'                   from dual union all
                                    select 'ZEUS MULTIMEDIA'                    from dual union all
                                    select 'IRIS PHONE'                         from dual union all
                                    select 'CAHAYA MUTIARA CELLULAR'            from dual union all
                                    select 'NEO PHONE'                          from dual union all
                                    select 'ALDILLA'                            from dual union all
                                    select 'VIA SELL'                           from dual union all
                                    select 'INTER SELL'                         from dual union all
                                    select 'FAMILY PHONE'                       from dual union all
                                    select 'ELECTRIC-CELL'                      from dual union all
                                    select 'ELECTRIC-CELL'                      from dual union all
                                    select 'ONE & ONE'                          from dual union all
                                    select 'SATRIA 87 CELLULAR'                 from dual union all
                                    select 'PACIFIC SELLULER'                   from dual union all
                                    select 'MEGAVON'                            from dual union all
                                    select 'PROXY'                              from dual union all
                                    select '8-STAR PHONE SHOP'                  from dual union all
                                    select 'EMERALD PHONE'                      from dual union all
                                    select 'OCTOPUS CELL'                       from dual union all
                                    select 'METRO PHONE SHOP'                   from dual union all
                                    select 'ABADI PHONE'                        from dual union all
                                    select 'D-PHONE CELL'                       from dual union all
                                    select 'PLANET MOBILE PHONE SHOP'           from dual union all
                                    select 'SSS (SELULER SELULER SELULER)'      from dual union all
                                    select 'FLASH PHONE'                        from dual union all
                                    select 'SPEED CELL'                         from dual union all
                                    select 'OWEN CELLULAR CERVICE'              from dual union all
                                    select 'EMPIRE'                             from dual union all
                                    select 'OWEN CELL'                          from dual union all
                                    select 'ORANGE PHONE'                       from dual union all
                                    select 'DIAMOND'                            from dual union all
                                    select 'HANSD PHONE'                        from dual union all
                                    select 'EFATA CELLULER'                     from dual union all
                                    select 'LAREES PHONE'                       from dual union all
                                    select 'XCELINDO'                           from dual union all
                                    select 'CANARY CELL'                        from dual union all
                                    select 'W@P SELULAR'                        from dual union all
                                    select 'NENG PHONE CELL'                    from dual union all
                                    select 'ZEUS MULTIMEDIA'                    from dual union all
                                    select 'SHAQUILA PHONE HOUSE'               from dual union all
                                    select 'TIARA CELLULAR'                     from dual union all
                                    select 'ONE BY ONE'                         from dual union all
                                    select 'LIONTIN CELULLAR'                   from dual union all
                                    select 'ELECTRIC-CELL'                      from dual union all
                                    select 'MACKINDO JAYA'                      from dual union all
                                    select 'LORDS CELL'                         from dual union all
                                    select 'INNOVA'                             from dual union all
                                    select 'CONCEPT COMUNICATION'               from dual union all
                                    select 'RAIHAN SELLULAR'                    from dual union all
                                    select 'KING CELLULAR'                      from dual union all
                                    select 'PRODYA CELL'                        from dual union all
                                    select 'SHAQUILA PHONE HOUSE'               from dual union all
                                    select 'AMOR PHONE'                         from dual union all
                                    select 'HIFONE'                             from dual union all
                                    select 'HANSD PHONE'                        from dual union all
                                    select 'MR PHONE CELLULAR'                  from dual union all
                                    select 'RAMAI PHONE'                        from dual union all
                                    select 'PRODYA CELL'                        from dual union all
                                    select 'DEVA JAYA CELL'                     from dual union all
                                    select 'MILLION CELL'                       from dual union all
                                    select 'AMORPHONE'                          from dual union all
                                    select 'R-CELL'                             from dual union all
                                    select 'MARCHEL'                            from dual union all
                                    select 'LINK PHONE'                         from dual union all
                                    select 'PONSEL 99'                          from dual union all
                                    select 'VINZO'                              from dual union all
                                    select 'ANUGRAH KOMUNIKA '                  from dual)
                                    
                                    
                                    
                                    
                                    alter table temp_sellout_mp_hy drop column NAMA_MALL
                                    alter table temp_sellout_mp_hy add (NAMA_MALL varchar2(20),kota varchar2(15),id number)
                                    update temp_sellout_mp_hy set kota='YOGYAKARTA6',NAMA_MALL='RAMAI MALL'
                                    Now your basic sample data is ready.
                                    next time you post please post it in the format above.
                                    Now comming to your query.
                                    Beleive this is what you need.
                                    UPDATE TEMP_SELLOUT_MP_HY x
                                       SET ID = (SELECT distinct ID
                                                   FROM REF_TOKO y
                                                  WHERE y.NAMA =x.NAMA_TOKO
                                                  )
                                     WHERE KOTA = 'YOGYAKARTA6'
                                       AND NAMA_MALL = 'RAMAI MALL'
                                       and exists (
                                       SELECT distinct ID
                                                   FROM REF_TOKO y
                                                  WHERE y.NAMA =x.NAMA_TOKO
                                       
                                       )
                                    Happy coding :)

                                    Cheers!!!
                                    Bhushan
                                    1 2 Previous Next