Update table with values of another table

Question

I'm trying to update a table via subquery but i get following error:

ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

I would like to insert the content of a third table in the column.

My query:

UPDATE GH212_TABLE1
SET GH212_LINK = (SELECT GH201_TABLE1.GH201_ATTRIBUTEX
                  FROM GH210_TABLE3
                  LEFT JOIN GH201_TABLE1
                  ON GH210_TABLE3.GH210_GH201_ID = GH201_TABLE1.GH201_ID
                  INNER JOIN GH212_TABLE1
                  ON GH212_TABLE1.GH212_GH210_ID = GH210_ID
                  WHERE GH212_TABLE1.GH212_GH210_ID=GH210_TABLE3.GH210_ID  
                  GROUP BY GH201_ATTRIBUTEX)

WHERE GH212_TABLE1.GH212_ATTRIBUTEY='11';

I'm not sure how to link the tables right, so that i get the attribute from one specific object.

Any help would be much appreciated.

Cheers, Fabi

EDIT: Thank you for your reply! I was well of the Problem with the multiple rows, but i somehow couldn't resolve it. Even when i tried the distinct query. But the solution from William Robertson seems to work, thanks a lot!!

Answer 1

A bit of a guess as I don't have your data or business logic, but perhaps you wanted something more like the following. I've removed the second instance of gh212_table1 from the subquery and instead linked t3 back to the table being updated:

update gh212_table1 t
set    gh212_link =
       ( select distinct t1.gh201_attributex
         from   gh210_table3 t3
                left join gh201_table1 t1 on t1.gh201_id = t3.gh210_gh201_id
         where  t3.gh210_id = t.gh212_gh210_id )
where  gh212_table1.gh212_attributey = '11';

There could still be a problem with this, though, if there are rows in gh212_table1 for which the subquery finds no row, as for those rows gh212_link will be set to null. If that is a problem, you might try rewriting it as a merge:

merge into gh212_table1 tgt
using ( select distinct t3.gh210_id, t1.gh201_attributex
        from   gh210_table3 t3
               left join gh201_table1 t1 on t1.gh201_id = t3.gh210_gh201_id ) src
on    ( src.gh210_id = tgt.gh212_gh210_id )
when  matched then update
      set tgt.gh212_link = src.gh201_attributex;

As you are fetching gh201_attributex from an outer join, it will be null whenever there is no row in gh201_table1 for a gh210_table3 row. Is that what you want? In the merge version, if you make it an inner join then the merge will only be applied where a row exists.