Getting error while using UPDATE with join

[Bottlenecked]

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.

[Praetor]

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.

[Katty]

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)

[kelfro]

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.

[Zecho]

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

[Bottlenecked]

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.