Update statement with inner join on Oracle ORA-01427

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

Answer

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');