UPDATE all values of a column with values of a related table I read so many Answers on this Website or in other blocks, but i just can't resolve this issue. I'm trying to perform an Update on a column of a table with values of an other, related table.

I'm working with ORACLE SQL Developer. And i mostly get the ORA-01427: single-row subquery returns more than one row. I'm totaly aware of what this error message is saying.
I only need the value of corresponding joined object to be written in on cell and for me it should work by declaring it with de join and with the WHERE condition. But maybe i'm missing something.

I tried:

UPDATE GH210_STELE 
SET GH210_STELE.GH210_GH220_ID =(SELECT GH220_AUSSTATTUNG.GH220_ID
                                 FROM GH210_STELE
                                 INNER JOIN GH220_AUSSTATTUNG
                                     ON GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER
                                 WHERE GH210_STELE.GH210_NUMMER=GH220_AUSSTATTUNG.GH220_INVENTAR_NR);


Or:

UPDATE GH210_STELE 
SET (GH210_GH220_ID, desc) = (SELECT GH220_AUSSTATTUNG.GH220_ID, 
                                     GH220_AUSSTATTUNG.desc 
                              FROM GH210_STELE 
                              INNER JOIN GH220_AUSSTATTUNG 
                                  ON GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER) 
WHERE EXISTS (SELECT 1
              FROM GH220_AUSSTATTUNG
              WHERE GH210_STELE.GH210_NUMMER = GH220_AUSSTATTUNG.GH220_INVENTAR_NR );


Or even:

MERGE INTO GH210_STELE 
USING (SELECT GH220_INVENTAR_NR, GH220_ID 
       FROM GH220_AUSSTATTUNG) GH220_AUSSTATTUNG 
    ON (GH220_AUSSTATTUNG.GH220_INVENTAR_NR = GH210_STELE.GH210_NUMMER) 
WHEN MATCHED THEN 
    UPDATE 
    SET GH210_STELE.GH210_GH220_ID = GH220_AUSSTATTUNG.GH220_ID;

UPDATE GH210_STELE a 
SET GH210_GH220_ID=(SELECT GH220_ID
                    FROM GH220_AUSSTATTUNG b
                    WHERE b.GH220_INVENTAR_NR = a.GH210_NUMMER);


Any help is much appreciated.

Cheers Fabi
Run the following select in order to find cases where you have more then one record for the key you are using. Once you find the records with duplicate values you can figure out how to deal with them and it could be removing them from the other table or updating the select to exclude them.

SELECT GH220_INVENTAR_NR, 
       COUNT(0) 
FROM GH220_AUSSTATTUNG 
GROUP BY GH220_INVENTAR_NR 
HAVING COUNT(0) &gt; 1