update表关联思路

本文探讨了使用UPDATE语句更新关联表的多种方法及其注意事项,包括处理空值问题、避免ORA-01427错误、使用高效更新策略及存储过程实现。

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

我做了一下UPDATE关联表的思路总结,希望对不知道的同学有帮助!

我最近一直想研究一下UPDATE语句,尤其是多表关联UPDATE的时候,很容易出问题,于是我就在PUB上问,在资料上查,现在我终于弄明白了。

对我帮助的帖子来源于 http://www.itpub.net/showthread. ... 10&pagenumber=1

我把实验的思路整理如下,希望对不知道的人有帮助!

我的结论是这样的
更新表的方式有三种方法
1、
其中最普通的是update t1 set b=(select b from t2 where t1.a=t2.a);
但是,要注意空值的影响,
如果怕空值的影响,要写成
update t1 set b= (select b from t2 where t1.a=t2.a)
where exists
(select 1 from t2 where t1.a=t2.a);


2、
update (
select /*+use_hash(t1,t2)*/ t1.b b1,t2.b b2
from t1,t2 where t1.a=t2.a)
set b1=b2;

这种方法效率高,但是要注意两个关联字段都要有唯一性索引!

3、存储过程

SQL> declare
2 cursor c is
3 select t1.*,t1.rowid from t1;
4 begin
5 for c1 in c loop
6 update t1 set b=
7 (select b from t2 where a=c1.a)
8 where rowid=c1.rowid
9 and
10 exists
11 (select 1 from t2 where c1.a=t2.a);
12 end loop;
13 end;
14 /

但是还是要注意要有exists的语句,否则一样解决不了空值问题

下面实验如下:

SQL> select * from t1;

A B
---------- ----------
1 1
2 2
3 4
4 4
1
2
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> update t1 set b=(select b from t2 where t1.a=t2.a);

已更新7行。

SQL> select * from t1;

A B
---------- ----------
1 2
2 5
3 7
4
1 2
2 5
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL>
现在ROLLBACK还原,还是原来表的记录如下,加EXISTS操作看看有什么变化
SQL> select * from t1;

A B
---------- ----------
1 1
2 2
3 4
4 4
1
2
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> update t1 set b= (select b from t2 where t1.a=t2.a)
2 where exists
3 (select 1 from t2 where t1.a=t2.a);

已更新5行。

SQL> select * from t1;

A B
---------- ----------
1 2
2 5
3 7
4 4
1 2
2 5
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL>


谢谢!
现在我实验明白了,如果不加
where exists
(select 1 from t2 where t1.a=t2.a);

t1表的a,b字段有4,4的一条记录,由于在t2表中a,b字段不存在a字段值为4的记录.这样在UPDATE的时候,在t2表中找不到就会用null去UPDATE t1表的4,4为4,null,这可不是我们愿意看到的.

但加了那个EXISTS,问题就避免了。


UPDATE关联表容易出现的第二个错误,也就是著名的
ORA-01427: 单行子查询返回多个行

在这里也能得到实验

回滚原来那两张表

SQL> rollback;

回退已完成。

SQL> select * from t1;

A B
---------- ----------
1 1
2 2
3 4
4 4
1
2
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> update t2 set b= (select b from t1 where t2.a=t1.a);
update t2 set b= (select b from t1 where t2.a=t1.a)
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行


SQL>
注意到,如果两张表完全一致,t1.a完全和t2.a一一对应,都是唯一的,那就不可能出现这样错误

如果两边不一致,比如t1的a值多余t2的a值或者反过来,我们就要特别注意了。

讲白点就是,多的表允许用少的表来更新,多的表的反正都被少的那个表更新(说准确点应该是少的表都要是唯一的a记录)

少的表,不允许被多的表来更新!

再举个例子
如果两边都有重复,那别管是利用t1更新t2还是利用t2更新t1都别想成功了

SQL> insert into t1 values (3,5);

已创建 1 行。

SQL> insert into t2 values (3,8);

已创建 1 行。

SQL> select * from t1;

A B
---------- ----------
3 5
1 1
2 2
3 4
4 4
1
2
4

已选择8行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6
3 8




SQL> update t1 set b=(select b from t2 where t1.a=t2.a);
update t1 set b=(select b from t2 where t1.a=t2.a)
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行


SQL> update t2 set b=(select b from t1 where t2.a=t1.a);
update t2 set b=(select b from t1 where t2.a=t1.a)
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行


SQL>


做完这些后,听说有另外一种方法能更高效的更新,于是再做实验

就是如下方法了,但是报错了


SQL> update (
2 select /*+use_hash(t1,t2)*/ t1.b b1,t2.b b2
3 from t1,t2
4 where t1.a=t2.a)
5 set b1=b2;
set b1=b2
*
第 5 行出现错误:
ORA-01779: 无法修改与非键值保存表对应的列

听说这种方法要保证两表都是唯一值才可以,只好删除掉重复记录



SQL> delete t1 where rowid in (select rid from(select rowid rid,row_number() ove
r(partition by a order by a desc) rn from t1 )where rn > 1) ;

已删除3行。

SQL> select * from t1;

A B
---------- ----------
1 1
3 4
2
4

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6
接着再更新,哇,又报错

SQL> update (
2 select /*+use_hash(t1,t2)*/ t1.b b1,t2.b b2
3 from t1,t2
4 where t1.a=t1.b)
5 set b1=b2;
set b1=b2
*
第 5 行出现错误:
ORA-01779: 无法修改与非键值保存表对应的列


是不是要建唯一性索引才可以呢?
SQL> create unique index indx_t1_a on t1(a);

索引已创建。


SQL> create unique index indx_t2_a on t2(a);

索引已创建。

再看看,这下可以了,看来网络上说的这种方法高效,但是也挺苛刻的,还要保证唯一性索引,两张表都要保证

SQL> update (
2 select /*+use_hash(t1,t2) */t1.b b1,t2.b b2
3 from t1,t2
4 where t1.a=t2.a)
5 set b1=b2;

已更新3行。

SQL>
SQL> select * from t1;

A B
---------- ----------
1 2
3 7
2 5
4

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

以上做了这么多实验,收获不小,也谢谢PUB上的兄弟的帮忙,不过后来想存储过程实现是不是也实验一把呢,算是对这个多表更新的一种全面的总结了。

再实验吧

SQL> rollback;

回退已完成。

SQL> select * from t1;

A B
---------- ----------
1 1
3 4
2
4

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> declare
2 cursor c is
3 select t1.*,t1.rowid from t1;
4 begin
5 for c1 in c loop
6 update t1 set b=
7 (select b from t2 where a=c1.a)
8 where rowid=c1.rowid;
9 end loop;
10 end;
11 /

PL/SQL 过程已成功完成。

SQL> select * from t1;

A B
---------- ----------
1 2
3 7
2 5
4

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL>

这个更新应该和普通的第一种更新要考虑的东西是一样的吧,会不会有空值的因素在里面呢?

测试,果然,这个存储过程更新的方法也要考虑空值才可以,否则也会不对,入下

SQL> select * from t1;

A B
---------- ----------
1 2
3 7
2 5
4

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> delete from t2 where a=1;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from t2;

A B
---------- ----------
2 5
3 7
6

SQL> declare
2 cursor c is
3 select t1.*,t1.rowid from t1;
4 begin
5 for c1 in c loop
6 update t1 set b=
7 (select b from t2 where a=c1.a)
8 where rowid=c1.rowid;
9 end loop;
10 end;
11 /

PL/SQL 过程已成功完成。

SQL> select * from t1;

A B
---------- ----------
1
3 7
2 5
4

SQL>

也是需要修改的,代码如下

SQL> select * from t1;

A B
---------- ----------
1 2
3 7
2 5
4

SQL> select * from t2;

A B
---------- ----------
2 5
3 7
6

SQL> declare
2 cursor c is
3 select t1.*,t1.rowid from t1;
4 begin
5 for c1 in c loop
6 update t1 set b=
7 (select b from t2 where a=c1.a)
8 where rowid=c1.rowid
9 and
10 exists
11 (select 1 from t2 where c1.a=t2.a);
12 end loop;
13 end;
14 /

PL/SQL 过程已成功完成。

SQL> select * from t1;

A B
---------- ----------
1 2
3 7
2 5
4

SQL>

这下没空值了

### Oracle Update Join 关联更新字段示例 在Oracle数据库中,可以通过多种方式实现基于关联的字段更新操作。以下是几种常见的方法及其适用场景: #### 方法一:快速游标更新法 这种方法适用于复杂逻辑下的多关联更新。通过将游标嵌套到 `FOR` 循环中,可以直接利用 `ROWID` 字段高效定位目标记录并完成更新。 ```sql BEGIN FOR cr IN ( SELECT a.rowid, b.join_state FROM t_join_situation a, t_people_info b WHERE a.people_number = b.people_number AND a.year = '2011' AND a.city_number = 'M00000' AND a.town_number = 'M51000' ) LOOP UPDATE t_join_situation SET join_state = cr.join_state WHERE ROWID = cr.ROWID; END LOOP; END; ``` 这种方式的优点是可以处理复杂的条件过滤和多层关联关系[^1]。 --- #### 方法二:Inline View 更新法 对于两之间存在主键关联的情况,推荐使用 Inline View 的方式进行更新。这种做法能够显著提升性能,尤其是在数据量较大的情况下。 ```sql UPDATE ( SELECT a.field_to_update AS old_value, b.new_value AS new_value FROM table_a a INNER JOIN table_b b ON a.key_column = b.foreign_key ) temp_table SET temp_table.old_value = temp_table.new_value; ``` 需要注意的是,如果目标列已经是期望值,则仍会被标记为已更新,这可能导致不必要的重复操作[^3]。 --- #### 方法三:Merge 更新法 当被更新的目标并非完全依赖于另一张的主键时,可以考虑使用 `MERGE` 语句来替代传统的 `UPDATE` 操作。它不仅支持插入缺失的数据行,还能同时满足现有记录的修改需求。 ```sql MERGE INTO discount d USING industry i ON (d.v_industry_code = i.v_industry_code) WHEN MATCHED THEN UPDATE SET d.v_parent_industry_code_o = i.v_parent_industry_code, d.v_parent_industry_name_o = m.v_industry_name FROM industry m WHERE i.v_parent_industry_code = m.v_industry_code; ``` 相比其他技术手段而言,`MERGE` 更加灵活且易于维护[^2]。 --- #### 方法四:标准 SQL 更新法(带子查询) 最基础也最常见的形式莫过于借助子查询完成跨间的信息同步工作了。尽管如此简单明了的设计思路容易理解掌握,但在某些特定条件下可能不如前面提到的技术那样高效快捷。 ```sql UPDATE table_a a SET field_to_update = ( SELECT b.new_value FROM table_b b WHERE a.key_column = b.foreign_key ); ``` 不过要注意一点就是,一旦遇到一对多的关系映射情况的话,那么上述代码就会报错提示找不到唯一的匹配项[^4]。 --- #### Java 中动态构建 SQL 并调用 PL/SQL 块 除了纯 SQL 层面的操作外,在实际项目开发过程中还经常需要用到编程语言配合后台脚本共同协作解决问题。比如下面这段 JAVA 程序片段展示了如何向 ORACLE 数据库提交一段包含 LEFT JOIN 的复合型更新请求: ```java String sql = "BEGIN FOR fn IN (" + "SELECT t.id, t.shbj, r.shbj AS shbj1 " + "FROM mj_ry_sg t " + "LEFT JOIN mj_ry r ON t.zgh = r.zgh " + "WHERE t.yxbj = '1' AND r.yxbj = '1' AND r.shbj IS NOT NULL" + ") LOOP " + "UPDATE mj_ry_sg t SET t.shbj = fn.shbj1 WHERE t.id = fn.id;" + "END LOOP; END;"; Db.update(sql); ``` 这里采用了显式的事务控制机制确保整个过程要么全部成功要么彻底失败回滚重试[^5]。 --- ### 总结 以上介绍了四种不同的 Oracle 关联更新策略,分别是 **快速游标更新法**、**Inline View 更新法**、**Merge 更新法** 和 **标准 SQL 子查询更新法**。每种方法各有优劣,需根据具体业务场景选择最适合的一种实施。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值