使用Oracle数据库的JOIN语法进行更新 [英] UPDATE with JOIN syntax for Oracle Database

问题描述

首先，我执行以下SQL语句.

First, I execute the following SQL statements.

drop table names;
drop table ages;

create table names (id number, name varchar2(20));
insert into names values (1, 'Harry');
insert into names values (2, 'Sally');
insert into names values (3, 'Barry');

create table ages (id number, age number);
insert into ages values (1, 25);
insert into ages values (2, 30);
insert into ages values (3, 35);

select * from names;
select * from ages;

因此，将创建以下表.

        ID NAME
---------- ----------
         1 Harry
         2 Sally
         3 Barry

        ID        AGE
---------- ----------
         1         25
         2         30
         3         35

现在，我想将Sally的年龄增加1，即将其设置为31.下面的查询可以正常工作.

Now, I want to update increment the age of Sally by 1, i.e. set it to 31. The following query works fine.

update ages set age = age + 1 where id = (select id from names where name = 'Sally');
select * from ages;

表格现在看起来像这样.

The table now looks like this.

        ID        AGE
---------- ----------
         1         25
         2         31
         3         35

我想知道是否有一种方法可以通过联接来完成.例如，我尝试了以下查询，但失败了.

I want to know if there is a way it can be done by joins. For example, I tried the following queries but they fail.

SQL> update ages set age = age + 1 from ages, names where ages.id = names.id and names.name = 'Sally';
update ages set age = age + 1 from ages, names where ages.id = names.id and names.name = 'Sally'
                              *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> update ages set age = age + 1 from names join ages on ages.id = names.id where names.name = 'Sally';
update ages set age = age + 1 from names join ages on ages.id = names.id where names.name = 'Sally'
                              *
ERROR at line 1:
ORA-00933: SQL command not properly ended

推荐答案

UPDATE语句的语法为:

The syntax of the UPDATE statement is:

http://docs.oracle.com/cd /B19306_01/server.102/b14200/statements_10007.htm

其中dml_table_expression_clause是:

请注意上述语法的( subquery )部分.

Please pay attention on ( subquery ) part of the above syntax.

subquery是一项允许执行联接更新的功能.

The subquery is a feature that allows to perform an update of joins.

最简单的形式可以是:

UPDATE (
   subquery-with-a-join
)
SET cola=colb

在更新联接之前，您必须了解此处列出的限制:

Before update a join, you must know restrictions listed here:

https://docs.oracle.com/cd /B28359_01/server.111/b28286/statements_8004.htm

该视图不得包含以下任何构造:

The view must not contain any of the following constructs:

• 集合运算符
• DISTINCT运算符
• 聚合或分析函数
• GROUP BY，ORDER BY，MODEL，CONNECT BY或START WITH子句
• SELECT列表中的集合表达式
• SELECT列表中的子查询
• 指定为WITH READ的子查询
• 加入(除某些例外)，如《 Oracle数据库管理员指南》中所述
• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
• A collection expression in a SELECT list
• A subquery in a SELECT list
• A subquery designated WITH READ ONLY
• Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

以及与可更新视图相关的通用规则-此处(第Updating a Join View节):
http://docs.oracle.com /cd/B19306_01/server.102/b14231/views.htm#sthref3055

and also common rules related to updatable views - here (section: Updating a Join View):
http://docs.oracle.com/cd/B19306_01/server.102/b14231/views.htm#sthref3055

联接视图的所有可更新列都必须映射到一个 保留键的表.请参阅密钥保存表"，以获取有关的内容的讨论. 保留键的表.如果视图是使用WITH CHECK定义的 OPTION子句，然后所有联接列和所有重复列 表不可更新.

All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable.

我们首先可以创建一个带有联接的子查询:

We can first create a subquery with a join:

SELECT age 
FROM ages a
JOIN names m ON a.id = m.id
WHERE m.name = 'Sally'

此查询仅返回以下结果:

This query simply returns the following result:

       AGE
----------
        30

现在我们可以尝试更新查询:

and now we can try to update our query:

UPDATE (
    SELECT age 
    FROM ages a
    JOIN names m ON a.id = m.id
    WHERE m.name = 'Sally'
)
SET age = age + 1;

但是我们得到一个错误:

but we get an error:

SQL错误:ORA-01779:无法修改映射到非键保留表的列

SQL Error: ORA-01779:cannot modify a column which maps to a non key-preserved table

此错误表示不满足上述限制之一(保留键的表).

This error means, that one of the above restriction is not meet (key-preserved table).

但是，如果我们向表中添加主键:

However if we add primary keys to our tables:

alter table names add primary key( id );
alter table ages add primary key( id );

然后，此更新可以正常进行，并且最终结果是:

then now the update works without any error and a final outcome is:

select * from ages;

        ID        AGE
---------- ----------
         1         25
         2         31
         3         35

这篇关于使用Oracle数据库的JOIN语法进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！