How to use update from in oracle

Question

I have a query used update...from...,it work in SQL Server, how can I make it work in oracle:

update t_user u set u.deptId = aa.id
from (select id, name from t_dept where name is not null) aa
where u.deptName = aa.name and u.deptId is null

Thanks.

Answer 1

Oracle does not have a from clause in update. One method uses merge. Here is another method:

update t_user
    set deptId = (select id
                  from t_dept d
                  where t_user.dept_Name = d.name
                 )
    where deptId is null and
          exists (select 1
                  from t_dept d
                  where t_user.dept_Name = d.name
                 );