oracle 多表关联更新为什么要加exists

最新推荐文章于 2024-07-22 15:27:09 发布

aoerqileng

最新推荐文章于 2024-07-22 15:27:09 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle管理

本文链接：https://blog.youkuaiyun.com/aoerqileng/article/details/79047105

oracle管理专栏收录该内容

145 篇文章

订阅专栏

本文详细探讨了在Oracle数据库中进行多表关联更新时使用EXISTS子句的原因。通过实例分析，解释了EXISTS如何帮助优化查询性能，避免全表扫描，确保更新操作的精确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> create table t1(id int,name varchar2(10));

Table created.

SQL> create table t2(id int,name varchar2(10));

Table created.

SQL> insert into t1 values(1,'a');

1 row created.

SQL> insert into t1 values(2,'b');

1 row created.

SQL> insert into t2 values(3,'c');

1 row created.

SQL> insert into t2 values(2,'d');

1 row created.

SQL> select * from t1;

    ID NAME
---------- ----------
     1 a
     2 b

SQL> select * from t2;

    ID NAME
---------- ----------
     3 c
     2 d

SQL> update t1 set name =(select name from t2 where t1.id=t2.id);

2 rows updated.

SQL> select * from t1;

    ID NAME
---------- ----------
     1
     2 d

SQL> delete from t1;

2 rows deleted.

SQL> delete from t2;

2 rows deleted.

SQL> insert into t1 values(1,'a');

1 row created.

SQL> insert into t1 values(2,'b');

1 row created.

SQL> insert into t2 values(3,'c');

1 row created.

SQL> insert into t2 values(2,'d');

1 row created.

SQL> update t1 set name =(select name from t2 where t1.id=t2.id) where exists(select 1 from t2 where t1.id=t2.id);

1 row updated.

SQL> select * from t1;

    ID NAME
---------- ----------
     1 a
     2 d