更新2个联接 [英] Update with 2 joins

问题描述

我正在尝试使用以下代码更新同一查询中2个不同表中的数据:

I'm trying to update data in 2 distinct tables in the same query with the following code:

UPDATE (SELECT s.passNumb, a.hour, a.minute, p.number, s.situation
        FROM passwords s
        JOIN atend a ON s.passNumb = a.passNumb
        JOIN points p ON a.number = p.number
        WHERE s.passNumb = 1 AND 
              p.number = 1)
  SET a.hour = TO_CHAR(SYSDATE, 'HH24'),
      a.minute = TO_CHAR(SYSDATE, 'MI'),
      s.situation = 'F';

但是我收到此错误:无法修改映射到非键保留表的列.我在做什么错了?

But I'm getting this error: Cannot modify a column which maps to a non key-preserved table. What am I doing wrong?

推荐答案

具有联接的视图(或在您的情况下包含联接的内联视图)必须满足以下条件才能更新:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm

A view with a join (or an inline view containing a join in your case) must meet the following conditions to be updatable:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm

如果您希望可更新联接视图，请执行以下所有操作条件必须为真:

DML语句只能影响连接基础的一个表.

对于INSERT语句，不得使用WITH CHECK创建视图OPTION，所有插入值的列都必须来自保留键的表.保留键的表是每个基表中的主键或唯一键值在联接视图.

For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.

对于UPDATE 语句，不得使用WITH CHECK创建视图OPTION和所有更新的列都必须从保留键的键中提取表格.

For an UPDATE statement, the view must not be created WITH CHECK OPTION, and all columns updated must be extracted from a key-preserved table.

---

第一个条件很明显: DML语句必须仅影响连接基础的一个表.

---

The first condition is rather obvious: The DML statement must affect only one table underlying the join.

但这是什么意思:"密钥保留表"?

But what does it mean: "key preserved table" ?

键保留表是每个主键或唯一键对应的表基表中的键值在联接视图中也是唯一的.

A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.

键保留表意味着该表中的每一行在视图结果中最多显示一次.

考虑一个简单的例子:

The key preserved table means that each row from this table will appear at most once in the result of a view.

Consider a simple example:

CREATE TABLE users(
  user_id int primary key,
  user_name varchar(100),
  age int
);

insert into users values(1,'Tom', 22);

CREATE TABLE emails(
  user_id int,
  email varchar(100)
);

Insert into emails values( 1, '[e-mail]' );
Insert into emails values( 1, '[e-mail]' );
commit;

还有一个联接:

SELECT * 
FROM users u
JOIN emails e ON u.user_id = e.user_id;

   USER_ID USER_NAME              AGE    USER_ID EMAIL              
---------- --------------- ---------- ---------- --------------------
         1 Tom                     22          1 [e-mail]   
         1 Tom                     22          1 [e-mail]  

如果您查看此联接的结果，显然:

If you look at a result of this join, it is apparent that:

• 用户名，用户名和年龄来自非密钥保留表
• 电子邮件来自保留键的表

现在:此更新是可以接受的，因为它会更新此联接中的键保留列(表):

And now: this update is acceptable, because it updates a key preserved column (table) in this join:

    UPDATE (
      SELECT * FROM users u
      JOIN emails e ON u.user_id = e.user_id
    )
    SET email = email || '.it' ; 

   USER_ID USER_NAME              AGE    USER_ID EMAIL                   
---------- --------------- ---------- ---------- -------------------------
         1 Tom                     22          1 [e-mail]     
         1 Tom                     22          1 [e-mail] 

---

但是此更新无法完成，因为它触及了非关键保留表中的一列:

---

But this update cannot be done, since it touches a column from non-key preserved table:

UPDATE (
  SELECT * FROM users u
  JOIN emails e ON u.user_id = e.user_id
)
SET age = age + 2; 

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

---

如果您想一会儿.... Tom在联接结果中出现2次(但 users 表中只有一个Tom).

当我们尝试更新此联接中的 age = age + 2 时，此更新应该是什么结果?

汤姆应该只更新一次吗?更新后，Tom应该22岁+ 2 = 24岁吗?

或者，也许Tom应该更新两次(因为它在联接结果中出现两次)，所以应该是22 + 2 + 2 = 26岁.

另一个例子-请告诉我此更新的结果是什么?:

---

If you think a while .... Tom appears 2 times in the result of the join (but there is only one Tom in the users table).

When we are trying to update age = age + 2 in this join, then what should be a result of this update ?

Should Tom be updated only once ? Should Tom be 22+2 = 24 years old after this update ?

Or maybe Tom should be updated twice (since it appears twice in the result of the join) so it should be 22 + 2 + 2 = 26 years old.

Another example - please tell me what should be an outcome of this update?:

UPDATE ( ....our join ... ) SET age = length( email );

有非常困难的问题:)
因此，Oracle禁止更新非关键保留表.

There are very difficult questions :)
And because of this Oracle prevents from updating non-key preserved tables.

错误消息给出以下提示: *操作:直接修改基础基表.

这意味着我们必须使用单独的UPDATE命令直接更新此表:

The error message gives this hint: *Action: Modify the underlying base tables directly.

This means that we must update this table directly, using a separate UPDATE command:

  UPDATE users SET age = age + 2

这篇关于更新2个联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！