来自同一表、不同行、Oracle 中、非空列的多列更新：接收错误 01407

Multi-column update from SAME table, different rows, in Oracle, with not-null column: Receiving error 01407

提问by Leanne

I'm getting an error I do not understand.

我收到一个我不明白的错误。

I have an Oracle database (11g, I think) with a table that has a multi-column unique identifier. It's a transaction table, taking a before and after snapshot of the data from a different table. So far so good, that's all working fine.

我有一个带有多列唯一标识符的表的 Oracle 数据库（我认为是 11g）。它是一个事务表，对来自不同表的数据进行前后快照。到目前为止一切顺利，一切正常。

We had a bug in our code and I'm trying to fix the data now that the bug is fixed. The bug resulted in the 'after' snapshot having two columns zeroed out in a circumstance where they should have kept their 'before'. So I'm going to update the 'after' snapshot (identified by a transaction type number) to have the same values in those two columns.

我们的代码中有一个错误，现在该错误已修复，我正在尝试修复数据。该错误导致“之后”快照的两列在应该保留“之前”的情况下归零。因此，我将更新“之后”快照（由交易类型编号标识）以在这两列中具有相同的值。

I've found all sorts of great help on the multi-column update thing, and I thinkI have it, except...it generates an error that it cannot update a not-null column to null, when there is no way I can see a null getting in there. Obviously, I am doing something wrong; I just can't tell what.

我在多列更新方面找到了各种很大的帮助，我想我有它，除了......它会产生一个错误，它无法将非空列更新为空，当我无法可以看到一个空值进入那里。显然，我做错了什么；我就是不知道是什么。

In some cases, one column can be null - there's an object type column that defines whether that record can have a null value.

在某些情况下，一列可以为空 - 有一个对象类型列定义该记录是否可以具有空值。

The table has this data in it, say:

该表中有此数据，例如：

object_name      object_type   trans_type   trans_date   quantity    actual_cost
no-quantity      1             1            04/16/2014   {null}      20.00
no-quantity      1             9            04/16/2014   {null}       0.00
needs quantity   2             1            04/16/2014   3           15.00
needs quantity   2             9            04/16/2014   0            0.00 

So I need to update the second row (no-quantity, trans_type 9) to actual_cost of 20; quantity can stay null. And I need the 4th row to be updated to quantity of 3, actual_cost of 15.

所以我需要将第二行（无数量，trans_type 9）更新为20的actual_cost；数量可以保持为空。我需要将第 4 行更新为数量 3，实际成本为 15。

Here is the query I'm trying to run that is failing - create and insert statements are below:

这是我尝试运行的查询失败 - 创建和插入语句如下：

update  demo_table new 
set     (quantity, actual_cost) = 
        (
        SELECT  quantity, actual_cost
        FROM    demo_table old
        WHERE   old.object_name = new.object_name
            and old.object_type = new.object_type
            and old.trans_date = new.trans_date
            and old.trans_type = 1
            and new.trans_type = 9
)

When I run this I get:

当我运行这个时，我得到：

ORA-01407: cannot update ("myschema"."DEMO_TABLE"."ACTUAL_COST") to NULL

ORA-01407: 无法将 ("myschema"."DEMO_TABLE"."ACTUAL_COST") 更新为 NULL

(I've tried the other way - update (select col1, col2, etc) - and the table's not set up right for it. Too bad, because that way looked easier to understand...)

（我已经尝试过另一种方式 - 更新（选择 col1、col2 等） - 并且表没有设置正确。太糟糕了，因为这种方式看起来更容易理解......）

Do I maybe need to have an outer where too? The other posts didn't indicate that I did, and I don't know how I would formulate it anyway.

我是否也需要在外面放一个外衣？其他帖子没有表明我这样做了，我也不知道我会如何制定它。

I even get the not-null error on this attempt:

我什至在这次尝试中得到了非空错误：

update  demo_table new 
set     (actual_cost) = 
        (
        SELECT  actual_cost
        FROM    demo_table old
        WHERE   old.object_name = new.object_name
            and old.object_type = new.object_type
            and old.trans_date = new.trans_date
            and old.trans_type = 1
            and new.trans_type = 9
            and old.object_type = 2
)

object_type of 2 doesn't have a null quantity, and anyway I'm not trying quantity in this one...

2 的 object_type 没有空数量，无论如何我不尝试在这个数量...

The select statement I run to check the actual where clause is correct looks like this:

我运行以检查实际 where 子句是否正确的 select 语句如下所示：

SELECT  old.object_name as old_name, old.object_type as old_type, old.trans_type as old_trans, old.trans_date as old_date,
    new.object_name as new_name, new.object_type as new_type, new.trans_type as new_trans, new.trans_date as new_date,
    old.quantity as old_quantity, old.actual_cost as old_cost, new.quantity as new_quantity, new.actual_cost as new_cost
FROM    demo_table old, demo_table new
WHERE    old.object_name = new.object_name
    and old.object_type = new.object_type
    and old.trans_date = new.trans_date
    and old.trans_type = 1
    and new.trans_type = 9

That gets the right values, the right old/new fields, only 2 rows returned, as I expect.

这得到了正确的值，正确的旧/新字段，只返回了 2 行，正如我所期望的。

Here are my create-and-insert statements:

这是我的创建和插入语句：

create table demo_table (
    object_name varchar2(30) not null,
    object_type number(3) not null,
    trans_type number(3) not null,
    trans_date timestamp(6) not null,
    quantity number(3),
    actual_cost number(17,2) not null
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'no-quantity', 1, 1, '16-APR-14', null, 20
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'no-quantity', 1, 9, '16-APR-14', null, 0
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'needs quantity', 2, 1, '16-APR-14', 3, 15
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'needs quantity', 2, 9, '16-APR-14', 0, 0
)

I hope my problem is clear. I did look around a lot, but I couldn't find anything that looked like it matched quite closely enough. The same-table thing is not really covered, and the not-nullable column is not really covered. (Or rather, it is, but I couldn't see how the problems described affected my situation.)

我希望我的问题很清楚。我确实环顾了很多，但我找不到任何看起来足够匹配的东西。同表的东西没有真正覆盖，不可为空的列也没有真正覆盖。（或者更确切地说，它是，但我看不出所描述的问题如何影响我的情况。）

I know the table setup is, shall we say, far from ideal. Can't fix it, tonight.

我知道桌子设置，容我们说，远非理想。今晚解决不了

采纳答案by Bob Jarvis - Reinstate Monica

First - +1 on the question - very well written, lots of supporting information, and please accept a huge THANK YOUfor providing enough information to solve the problem, including table def and statements to populate the table.

首先 - 在这个问题上 +1 - 写得很好，有很多支持信息，请接受非常感谢，感谢您提供足够的信息来解决问题，包括表定义和填充表的语句。

The only real issue with the first update was that the line in the WHEREclause which subsets the NEWtable should be pulled out of the subquery and put into the WHEREclause of the UPDATEstatement, as in:

与第一次更新的唯一真正的问题是，在该行WHERE该子集的子句NEW表应的子查询中拉出，并投入WHERE了的条款UPDATE声明，如下所示：

update  demo_table new 
set     (new.quantity, new.actual_cost) = 
        (
        SELECT  old.quantity, old.actual_cost
        FROM    demo_table old
        WHERE   old.object_name = new.object_name
            and old.object_type = new.object_type
            and old.trans_date = new.trans_date
            and old.trans_type = 1
)
where new.trans_type = 9;

I had to fool with this one for quite a while to figure out what was going on. Interesting problem.

我不得不在这个问题上愚弄了很长一段时间才能弄清楚发生了什么。有趣的问题。

SQLFiddle here.

SQLFiddle 在这里。

Share and enjoy.

分享和享受。