How to update a table using a CTE in Oracle

Question

As an example in Sql server I can achieve this easily like so:

WITH cte_person AS
(
     SELECT PersonalIdentificationNumber, PersonName
     FROM Employee
)
UPDATE Person
SET Person.PersonName = cte.PersonName
FROM cte_person cte
WHERE Person.PersonalIdentificationNumber = cte.PersonalIdentificationNumber

But how do I achieve this in Oracle using a CTE specifically or is there no support for this? I have searched around and havn't found a satisfying answer. Most seem to wrap the CTE in an inline select statement.

Answer 1

Well, as you asked how to use a CTE in UPDATE, then:

update person p set
  p.name = (with cte_person as
              (select personalidentificationnumber, name
               from employee
              )
            select c.name
            from cte_person c
            where c.personalidentificationnumber = p.personalidentificationnumber
           )
  where exists (select null
                from employee e
                where e.personalidentificationnumber = p.personalidentificationnumber
               );

Though, merge is somewhat simpler as you don't have to additionally check which rows to update (see the exists clause in update example):

merge into person p
  using (with cte_person as
              (select personalidentificationnumber, name
               from employee
              )
         select c.personalidentificationnumber,
                c.name
         from cte_person c
        ) x
  on (p.personalidentificationnumber = x.personalidentificationnumber)
  when matched then update set 
    p.name = x.name;        

---

However, this can be simplified - see code Ankit posted (but - as I said - if you want to know how to use a CTE, then that's how).