基于子查询的更新失败

Question

我正在尝试在 Oracle 10gR2 中进行以下更新：

update
  (select voyage_port_id, voyage_id, arrival_date, port_seq,
    row_number() over (partition by voyage_id order by arrival_date) as new_seq
   from voyage_port) t
set t.port_seq = t.new_seq

Voyage_port_id 是主键，voyage_id 是外键。我正在尝试根据每次航程中的日期分配一个序列号。

但是，上述操作失败，并显示ORA-01732：数据操作操作在此视图上不合法

有什么问题以及如何避免它？

Answer 1

由于您无法使用 row_number 更新子查询，因此您必须在更新的 set 部分计算行号。起初我尝试了这个：

update voyage_port a
set a.port_seq = (
  select 
    row_number() over (partition by voyage_id order by arrival_date)
  from voyage_port b
  where b.voyage_port_id = a.voyage_port_id
)

但这不起作用，因为子查询只选择一行，然后 row_number() 始终为 1。使用另一个子查询可以得到有意义的结果：

update voyage_port a
set a.port_seq = (
  select c.rn
  from (
      select 
        voyage_port_id
      , row_number() over (partition by voyage_id 
            order by arrival_date) as rn
      from voyage_port b
   ) c
  where c.voyage_port_id = a.voyage_port_id
)

它可以工作，但是比我对这项任务的预期更复杂。

Answer 2

您可以更新某些视图，但有一些限制，其中之一是视图不得包含分析函数。请参阅有关 UPDATE 的 SQL 语言参考并搜索第一次出现的“analytic”。

如果航程在同一天访问多个港口（或者日期包含使它们独一无二的时间部分），则这将起作用：

update voyage_port vp
set vp.port_seq =
( select count(*)
  from voyage_port vp2
  where vp2.voyage_id = vp.voyage_id
  and vp2.arrival_date <= vp.arrival_date
)

我认为这可以处理航程每天访问超过 1 个港口且没有时间部分（尽管同一天访问的端口顺序是任意的）：

update voyage_port vp
set vp.port_seq =
( select count(*)
  from voyage_port vp2
  where vp2.voyage_id = vp.voyage_id
  and (vp2.arrival_date <= vp.arrival_date)
  or (   vp2.arrival_date = vp.arrival_date 
     and vp2.voyage_port_id <= vp.voyage_port_id
     )
)

Answer 3

不要认为您可以更新派生表，我将重写为：

update voyage_port
set port_seq = t.new_seq
from
voyage_port p
inner join
  (select voyage_port_id, voyage_id, arrival_date, port_seq,
   row_number() over (partition by voyage_id order by arrival_date) as new_seq
   from voyage_port) t
on p.voyage_port_id = t.voyage_port_id

Answer 4

UPDATE 之后的第一个标记应该是要更新的表的名称，然后是要更新的列。我不确定您想通过 select 语句实现什么目的，但您可以合法地更新 select 的结果集。
猜测您的想法，sql 的一个版本可能看起来像...

update voyage_port t
set t.port_seq = (<select statement that generates new value of port_seq>)

注意： 要使用 select 语句来设置这样的值，您必须确保仅返回 1 行选择！

编辑：修改上面的语句以反映我试图解释的内容。上面的安多马尔已经很好地回答了这个问题