Update statement with inner join on Oracle ORA-01427-CodePudding

UPDATE Team 
SET Team_name = CONCAT((SELECT Team_name 
                        FROM Team 
                        INNER JOIN Coach ON Team.Coach_id = Coach.Coach_id 
                        WHERE Coach_name = 'Sidny Jonson'), '_nure') ;

ORA-01427: single-row subquery returns more than one row ORA-06512:
at "SYS.DBMS_SQL", line 1721

I need to do update with inner join in ORACLE

CodePudding user response：

The script you provided above is going to update all rows in the table because you do not have a where clause. If you really want to update all rows in the table, then this should work.

UPDATE team SET team_name = team_name||'_nure';

If you only want to update the rows for coach Sidny Jonson, then this should work.

UPDATE team SET team_name = team_name||'_nure' where coach_id in (select coach_id from Coach where Coach_name = 'Sidny Jonson');

CodePudding user response：

To me, it looks like

update team a set
  a.team_name = a.team_name || '_nure'
where exists (select null 
              from coach b
              where b.coach_id = a.coach_id
                and b.coach_name = 'Sidny Jonson'
             );

or - using merge:

merge into team a
  using coach b
  on (a.coach_id = b.coach_id)
when matched then update set
  a.team_name = a.team_name || '_nure'
  where b.coach_name = 'Sidny Jonson';