在许多DBMS中，您只需编写带有(self)join的查询，然后将其转换为update语句即可. .但是Oracle不允许在更新语句中加入连接.

In a lot of DBMSs, you can just write a query with the (self)join, then convert that to an update statement with a minimum of effort. But Oracle doesn't allow joins in an update statement.

所以我们作弊.

首先，将查询编写为联接.如果没有其他要求，我们可以看到要更改的行与包含要更改为的数据的相应行相匹配.

First, write the query as a join. If nothing else, we can see the row(s) we want to change matched with the corresponding row containing the data to change it to.

select  *
from    LoanActions la1
join    LoanActions la2
    on  la2.Emp_ID = 1234567
    and la2.Seq_No = 3
where   la1.Emp_ID = 1234567
    and la1.Seq_No =(
        select  Max( Seq_No )
        from    LoanActions
        where   Emp_ID = 1234567
            and Action_Status = 'R');

表 la1 包含要更改的数据， la2 包含要更改的数据.现在，只需以Oracle允许的形式编写update.在set子句中，列出要更改的字段和提供新数据的子查询.这将在上面的 la2 中，子查询的where子句将包含查询中的联接条件. update语句的where子句主要由exists需求和(可能相关的)子查询组成，该子查询提供了键值列表以匹配要更新的行.这将与现有update语句的where子句紧密匹配. Oracle要求将它们作为键值.

Table la1 contains the data to change, la2 the data to change it to. Now just write the update in a form Oracle allows. In the set clause, list the fields to change and a subquery to provide the new data. This will be la2 above and the where clause of the subquery will contain the join criteria from the query. The where clause of the update statement mainly consists of an exists requirement with a (possibly correlated) subquery providing a list of key values to match the rows you want updated. This will closely match the where clause of your existing update statement. Oracle requires these to be key values.

那么完成的语句是:

update  LoanActions la1
    set la1.Action_Status = 'B',
        (la1.Action_Date, la1.Xfer_Batch )=(
        select  la2.Action_Date, la2.Xfer_Batch
        from    LoanActions  la2
        where   la2.Emp_ID = 1234567
            and la2.Seq_no = 3 )
where   exists(
        select  Max( Seq_No )
        from    LoanActions
        where   Emp_ID = 1234567
            and Action_Status = 'R'
            and Emp_ID = 1234567 );

请注意，这是基于您提供的少量数据.您的update语句引用的是您从示例数据中省略的字段，因此我必须进行补充. (并且我从对象名称中删除了一些内容.)因此，该语句可能需要在您的环境中进行一些细微调整.但是，这为您提供了一个起点.

Note that this is based on the scant data you provided. Your update statement refers to fields you omitted from the sample data so I had to make up something. (And I trimmed just a bit from the object names.) So the statement may well require some slight adjustment in your environment. But this gives you a starting point if nothing else.

请注意，要添加要修改的列，只需将其添加到set列表中即可.

Note also that to add columns to be modified, just add them to the set list.