Оператор обновления с использованием предложения

У меня есть script, который использует стек с предложениями для получения некоторого результата, а затем я хочу записать этот результат в таблицу. Я просто не могу опустить голову, может ли кто-нибудь указать мне в правильном направлении?

Здесь приведен упрощенный пример, который указывает, что я хочу сделать:

with comp as (
  select *, 42 as ComputedValue from mytable where id = 1
)
update  t
set     SomeColumn = c.ComputedValue
from    mytable t
        inner join comp c on t.id = c.id

В реальной ситуации есть немало статей с предложениями о том, что все ссылки друг на друга, поэтому любые предложения, на самом деле использующие предложение with, были бы крайне предпочтительны при реорганизации его в вложенные подзапросы.

Спасибо заранее,

Герт-Ян

Ответ 1

Если кто-то приходит сюда после меня, это ответ, который сработал у меня.

update mytable t
set z = (
  with comp as (
    select b.*, 42 as computed 
    from mytable t 
    where bs_id = 1
  )
  select c.computed
  from  comp c
  where c.id = t.id
)

Удачи,

ГДж

Ответ 2

Синтаксис WITH представляется действительным во встроенном представлении, например

UPDATE (WITH comp AS ...
        SELECT SomeColumn, ComputedValue FROM t INNER JOIN comp ...)
   SET SomeColumn=ComputedValue;

Но в быстрых тестах я всегда проигрывал с ORA-01732: data manipulation operation not legal on this view, хотя это удалось, если я переписал, чтобы исключить предложение WITH. Таким образом, рефакторинг может помешать возможности Oracle гарантировать сохранение ключей.

Однако вы должны иметь возможность использовать MERGE. Используя простой пример, который вы опубликовали, даже не требует предложения WITH:

MERGE INTO mytable t
USING (select *, 42 as ComputedValue from mytable where id = 1) comp
ON (t.id = comp.id)
WHEN MATCHED THEN UPDATE SET SomeColumn=ComputedValue;

Но я понимаю, что у вас более сложный подзапрос, который вы хотите отменить. Я думаю, что вы сможете сделать подзапрос в предложении USING произвольно сложным, включая несколько предложений WITH.

Ответ 3

Вы всегда можете сделать что-то вроде этого:

update  mytable t
set     SomeColumn = c.ComputedValue
from    (select *, 42 as ComputedValue from mytable where id = 1) c
where t.id = c.id

Теперь вы можете также использовать с выражением внутри обновления

update  mytable t
set     SomeColumn = c.ComputedValue
from    (with abc as (select *, 43 as ComputedValue_new from mytable where id = 1
         select *, 42 as ComputedValue, abc.ComputedValue_new  from mytable n1
           inner join abc on n1.id=abc.id) c
where t.id = c.id