Using update from the same table with ORACLE

November 30, 2021

I’m trying to update a field from my table using another field from the same table, but when I run the update below I get an error

update f1
set f1.usu_cgcstr = f2.cgccpf
from E095for as f1
join E095for as f2
on f1.codfor = f2.codfor
where  f1.usu_intot = 'N'

Error:

00000 – "SQL command not properly ended"

So how I can do this update?

>Solution :

Wrong syntax; use merge:

merge into f1
  using e095for f2
  on (f1.codfor = f2.codfor)
  when matched then update set f1.usu_cgcstr = f2.cgccpf
  where f1.usu_intot = 'N';

Or, if you wanted update:

update e095for f1 set
  f1.usu_intot = (select f2.cgccpf
                  from e095for f2
                  where f2.codfor = f1.codfor
                 )
  where f1.usu_intot = 'N'
    and exists (select null from e095for c
                where c.codfor = f1.codfor
               );