Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Getting error while using UPDATE with join

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 18-01-2010
Member
 
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':

Quote:
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.

Reply With Quote
  #2  
Old 18-01-2010
Member
 
Join Date: Apr 2008
Posts: 1,947
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.
Reply With Quote
  #3  
Old 18-01-2010
Member
 
Join Date: May 2008
Posts: 2,007
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:

Quote:
Update table T01 set T01.Champ01 = ( select T02.Champ01
From table T02
Where
T01.Champ02 = T02.Champ02)
Reply With Quote
  #4  
Old 18-01-2010
Member
 
Join Date: Apr 2008
Posts: 2,000
Re: Getting error while using UPDATE with join

I think you should refer the below query to update your table using another table:
Quote:
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.
Reply With Quote
  #5  
Old 18-01-2010
Member
 
Join Date: May 2008
Posts: 2,291
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
Reply With Quote
  #6  
Old 18-01-2010
Member
 
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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Getting error while using UPDATE with join"
Thread Thread Starter Forum Replies Last Post
Age of Empires 3 Failed to join game Error m3pilot Windows XP Support 1 21-03-2013 12:46 PM
Canít join friends in Max Payne 3, error "Join Failed Failure: Blacklisted. G.Kahli Video Games 3 02-06-2012 12:44 PM
DNS error 0x0000232b when trying to join the domain. Mark J. Ankney Windows Server Help 4 24-02-2011 11:28 PM
Update with Inner join on same table stracker.phil Software Development 1 16-12-2009 01:13 PM
Update with Inner Join SQL Server Coldman Software Development 5 25-02-2009 09:22 PM


All times are GMT +5.5. The time now is 05:49 PM.