使用另一行中的字段更新字段 [英] Update fields using fields from another row

问题描述

我有2行:

• 第1行(LN_DISB_SEQ_NBR = 3)此行包含我需要的字段数据
• 第2行(max(LN_DISB_SEQ_NBR)= 6)此行包含我要更新的字段

• Row #1 (LN_DISB_SEQ_NBR = 3) This row has the field data I need
• Row #2 (max(LN_DISB_SEQ_NBR) = 6) This row has fields I want to update

我所拥有的示例(不是完整的表格):

Example of what I have (not the full table):

LN_DISB_SEQ_NBR | EMPLID  | DNT_CHNG_THS | LN_ACTION_DT | TRNSFR_BATCH | more...
----------------+---------+--------------+--------------+--------------+--------
              3 | 1234567 |          abc |  13-JAN-2015 | TB3399999203 |
              6 | 1234567 |          xyz |  14-JAN-2015 | TB4820000000 | 

我想要的更新示例:

LN_DISB_SEQ_NBR | EMPLID  | DNT_CHNG_THS | LN_ACTION_DT | TRNSFR_BATCH | more...
----------------+---------+--------------+--------------+--------------+--------
              3 | 1234567 |          abc |  13-JAN-2015 | TB3399999203 |
              6 | 1234567 |          xyz |  13-JAN-2015 | TB3399999203 | 

这是我看到的问题:现在，我的SQL运行良好，但是我对它的构造方式不满意.如果我想添加更多字段以进行更改，它将不断增长，并且将变得越来越难以维护(必须更改多个字段).

Here's the issue I'm seeing: Right now, my SQL works golden, but I'm not happy with the way it's constructed. If I want to add more fields to change, it will continue to grow in size and continually get more difficult to maintain (having to change a number of fields).

我该如何重写/压缩它，使其执行相同的任务，但效率更高，更清洁?

update PS_AG_LOAN_DISB_ACTN3 t1
   set t1.LN_ACTION_STATUS = 'B',
       t1.LN_ACTION_DT = (select LN_ACTION_DT
                            from PS_AG_LOAN_DISB_ACTN3
                           where EMPLID = '1234567'
                             and ITEM_TYPE = '913000300110'
                             and LN_DISB_SEQ_NBR = 3),
       t1.TRNSFR_BATCH = (select TRNSFR_BATCH
                            from PS_AG_LOAN_DISB_ACTN3
                           where EMPLID = '1234567'
                             and ITEM_TYPE = '913000300110'
                             and LN_DISB_SEQ_NBR = 3                    
 where t1.EMPLID = '1234567'
   and t1.LN_DISB_SEQ_NBR = (select max(LN_DISB_SEQ_NBR)
                               from PS_AG_LOAN_DISB_ACTN3
                              where EMPLID = '1234567'
                                and ITEM_TYPE = '913000300110'
                                and LN_ACTION_STATUS = 'R');

推荐答案

在许多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.

这篇关于使用另一行中的字段更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！