Results 1 to 6 of 6

Thread: Getting error while using UPDATE with join

  1. #1
    Join Date
    Dec 2009
    Posts
    67

    Getting error while using UPDATE with join

    Hi Friends,

    I am using Oracle 10g and I am trying to run below query to 'update' table using 'join':

    UPDATE TABLE T01 SET T01.Champ01 =SELECT T02.Champ01
    FROM TABLE T02
    TABLE INNER JOIN T01 ON T01.Champ02 = T02.Champ02
    I am getting error while running this query. I am not able to find out the exact cause of this error. Please let me what changes I need to do to run this query without ant error.

  2. #2
    Join Date
    Apr 2008
    Posts
    1,949

    Re: Getting error while using UPDATE with join

    Hi friend,

    When you are using very complex query in the oracle you will definitely get the error known as 'single row sub query returns more than one row" with error code '1427'. I think you are getting same type of error. Mostly when you use the join command to update the table it will mostly generate error. If you want to avoid this error I suggest you to use any equivalent command to the 'join' command. I hope it may solve your problem.

  3. #3
    Join Date
    May 2008
    Posts
    2,015

    Re: Getting error while using UPDATE with join

    Hi,

    It sounds that you query is faulty. I suggest you to try something below query instead you you mentioned query:

    Update table T01 set T01.Champ01 = ( select T02.Champ01
    From table T02
    Where
    T01.Champ02 = T02.Champ02)

  4. #4
    Join Date
    Apr 2008
    Posts
    2,010

    Re: Getting error while using UPDATE with join

    I think you should refer the below query to update your table using another table:
    UPDATE ADDRESS1 AD1
    SET AD1.Libelle_pays1 = (SELECT REF1.Libelle_pays1
    FROM REFERENTIEL_PAYS REF1
    WHERE AD1.Code_Pays1 = REF1.Code_Pays1)
    if you have "single-row subquery returns more than one row" 1427, because you have multiple lines for one country_code REFERENTIEL_PAYS1 in your table, and therefore has less to a separate front REF1.Libelle_pays1 you'll need establish a rule.

  5. #5
    Join Date
    May 2008
    Posts
    2,302

    Re: Getting error while using UPDATE with join

    Hello friend,

    I think this issue of '' Update and Join' command is occurred due to the duplication in the reference table. As you are using the 'T02' value to update the values in the table 'T01'. I seems that there may be duplicate values are available in the reference table 'T02'. I recommend you to first check the table 'T02' for the the duplicate values. I you find any duplicate value then remove that entry. Then again try to run this query and if you are still getting same problem or not

  6. #6
    Join Date
    Dec 2009
    Posts
    67

    Re: Getting error while using UPDATE with join

    Hi Friends,

    Thanks for your instant and correct suggestion. I checked the second reference table and found that this 'T02' table comprises some duplicate entries for some column. As per your suggestion I removed that duplicate entries and again tried to run my query to update table with join command. And his time I didn't get any error while executing this query in oracle. Also the output of the query is quit correct as per the desired output result. Again thanks to all your help.

Similar Threads

  1. Age of Empires 3 Failed to join game Error
    By m3pilot in forum Windows XP Support
    Replies: 1
    Last Post: 21-03-2013, 12:46 PM
  2. Replies: 3
    Last Post: 02-06-2012, 12:44 PM
  3. DNS error 0x0000232b when trying to join the domain.
    By Mark J. Ankney in forum Windows Server Help
    Replies: 4
    Last Post: 24-02-2011, 11:28 PM
  4. Update with Inner join on same table
    By stracker.phil in forum Software Development
    Replies: 1
    Last Post: 16-12-2009, 01:13 PM
  5. Update with Inner Join SQL Server
    By Coldman in forum Software Development
    Replies: 5
    Last Post: 25-02-2009, 09:22 PM

Posting Permissions

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