oracle 连表update SQL操作注意事项：exists语句对解决空值问题至关重要？

14 /

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

下面实验如下：

SQL> * from t1;

A B

---------- ----------

1 1

2 2

3 4

4 4

已选择7行。

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

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

已更新7行。

SQL> * from t1;

A B

---------- ----------

1 2

2 5

3 7

1 2

2 5

已选择7行。

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

SQL>

现在ＲＯＬＬＢＡＣＫ还原，还是原来表的记录如下，加ＥＸＩＳＴＳ操作看看有什么变化

SQL> * from t1;

A B

---------- ----------

1 1

2 2

3 4

4 4

已选择7行。

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

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

2 where

3 ( 1 from t2 where t1.a=t2.a);

已更新5行。

SQL> * from t1;

A B

---------- ----------

1 2

2 5

3 7

4 4

1 2

2 5

已选择7行。

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

SQL>

谢谢!

现在我实验明白了，如果不加

where

( 1 from t2 where t1.a=t2.a);

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

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

关联表容易出现的第二个错误,也就是著名的

ORA-01427: 单行子查询返回多个行

在这里也能得到实验

回滚原来那两张表

SQL> ;

回退已完成。

SQL> * from t1;

A B

---------- ----------

1 1

2 2

3 4

4 4

已选择7行。

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

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

t2 set b= ( 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> into t1 (3,5);

已创建 1 行。

SQL> into t2 (3,8);

已创建 1 行。

SQL> * from t1;

A B

---------- ----------

3 5

1 1

2 2

3 4

4 4

已选择8行。

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

3 8

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

t1 set b=( b from t2 where t1.a=t2.a)

第 1 行出现错误:

ORA-01427: 单行子查询返回多个行

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

t2 set b=( b from t1 where t2.a=t1.a)

第 1 行出现错误:

ORA-01427: 单行子查询返回多个行

SQL>

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

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

SQL> (

2 /*+(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> t1 where rowid in ( rid from( rowid rid,() ove

r( by a order by a desc) rn from t1 )where rn > 1) ;

已删除3行。

SQL> * from t1;

A B

---------- ----------

1 1

3 4

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

接着再更新,哇,又报错

SQL> (

2 /*+(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> index on t1(a);

索引已创建。

SQL> index on t2(a);

索引已创建。

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

SQL> (

2 /*+(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> * from t1;

A B

---------- ----------

1 2

3 7

2 5

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

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

再实验吧

SQL> ;

回退已完成。

SQL> * from t1;

A B

---------- ----------

1 1

3 4

SQL> * from t2;

A B

---------- ----------

1 2

2 5

3 7

SQL>

2 c is

3 t1.*,t1.rowid from t1;

4 begin

5 for c1 in c loop

6 t1 set b=

7 ( b from t2 where a=c1.a)

8 where rowid=c1.rowid;

SQL ---------- from t1 t2 where