Блог

Главная - Вопросы по программированию - Как обновить таблицу с помощью CTE в Oracle

Как обновить таблицу с помощью CTE в Oracle

#sql #oracle #oracle12c

Вопрос:

В качестве примера в Sql server я могу легко добиться этого, вот так:

 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

Но как мне добиться этого в Oracle, используя CTE специально, или для этого нет поддержки? Я поискал вокруг и не нашел удовлетворительного ответа. Большинство из них, похоже, заключают CTE во встроенную инструкцию select.

Комментарии:

1. Oracle не разрешает этот синтаксис. Пожалуйста, предоставьте примеры данных, желаемые результаты и объяснение того, что должен делать код. Примечание: Ваш код также не является допустимым кодом SQL Server, поэтому совершенно неясно, что вы пытаетесь сделать.

Ответ №1:

Ну, поскольку вы спросили , как использовать CTE в UPDATE , то:

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

Хотя merge это несколько проще, так как вам не нужно дополнительно проверять, какие строки следует обновлять (см. exists Предложение в update примере).:

 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;

Однако это можно упростить — см. Опубликованный код Ankit (но, как я уже сказал, если вы хотите знать, как использовать CTE, то вот как).

Комментарии:

1. Рад видеть последнюю строчку вашего ответа. Это много значит.

2. В то время как ответ Анкита в данном случае действительно проще. Вопрос в том, как конкретно это сделать с CTE, чего, к сожалению, он не сделал. Я рад, что вы нашли время, чтобы тщательно прочитать вопрос, я проверю это, и если он работает так, как ожидалось, и для этого нет более чистого синтаксиса, вы также получите ответ.

Ответ №2:

Oracle не позволяет напрямую обновлять CTE (в отличие от SQL Server, который это позволяет). Если я правильно понимаю ваше требование, вы хотите обновить имена в Person таблице, используя имена из Employee таблицы, на основе совпадающих идентификаторов. Один из способов сделать это в Oracle использует коррелированный подзапрос.

 UPDATE Person p
SET Name = (SELECT e.Name FROM Employee e
            WHERE e.PersonalIdentificationNumber = p.PersonalIdentificationNumber);

Комментарии:

1. Спасибо за ваш вклад. Я дал вам указание указать, что Oracle не поддерживает этот синтаксис, но вопрос в том, как использовать CTE.

Ответ №3:

Для этого вы можете использовать инструкцию MERGE —

 MERGE INTO Person p
USING Employee e
ON (p.PersonalIdentificationNumber = e.PersonalIdentificationNumber)
WHEN MATCHED THEN
             UPDATE
                SET p.Name = e.Name;

Комментарии:

1. Спасибо за ваш вклад, Анкит, но я уже знаю о синтаксисе слияния сам по себе. Вопрос заключался в том, как это сделать с помощью CTE.

2. «Вопрос заключался в том, как это сделать с помощью CTE». Мне любопытно, почему настаивают на использовании CTE, когда есть лучшие решения.

Метки: