表连接的update,经典？.txt

来自「oracle问题集」· 文本 代码 · 共 1,026 行 · 第 1/2 页

ITPUB论坛 - 表连接的update,经典？
ITPUB论坛 (http://www.itpub.net/index.php)
- Oracle开发 (http://www.itpub.net/forumdisplay.php?forumid=3)
-- 表连接的update,经典？ (http://www.itpub.net/showthread.php?threadid=45822)


由 biti_rainy 于 02-07-29 16:32 发表: 
 表连接的update,经典？
SQL> select * from test;

A B C D
---------- ---------- ---------- --------------------
1 1 1 1
2 2 2 2
3 3 3 wew

已用时间: 00: 00: 00.00
SQL> select * from test1;

未选定行

已用时间: 00: 00: 00.00
SQL> insert into test1 values(1,9);

已创建 1 行。

已用时间: 00: 00: 00.00
SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
已用时间: 00: 00: 00.00
SQL> update (select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a = 
b.a ) set ab = bb;
update (select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a = b.a ) 
set ab = bb
*
ERROR 位于第 1 行:
ORA-01779: 无法修改与非键值保存表对应的列


已用时间: 00: 00: 00.01
SQL> alter table test1 add primary key(a);

表已更改。

已用时间: 00: 00: 00.05
SQL> update (select a.a aa,a.b ab,b.a ba,b.b bb from test a,test1 b where a.a = 
b.a ) set ab = bb;

已更新 1 行。

已用时间: 00: 00: 00.00
SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
SQL> select * from test;

A B C D
---------- ---------- ---------- --------------------
1 9 1 1
2 2 2 2
3 3 3 wew

已用时间: 00: 00: 00.00
SQL> 

注意test1表做表连接的时候，必须具有唯一标志，也就是如上加个主键就可以了
__________________
I love oracle
But i hate IT

oracle文档
http://download-west.oracle.com/docs/cd/A87861_01/NT817EE/index.htm


由 biti_rainy 于 02-07-29 16:41 发表: 
表连接的delete
SQL> delete (select a.a aa,a.b ab,a.c ac,a.d ad from test a,test1 b where a.a = 
b.a );

已删除 1 行。
SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
SQL> select *from test;

A B C D
---------- ---------- ---------- --------------------
2 2 2 2
3 3 3 wew

已用时间: 00: 00: 00.00
SQL> select * from test1;

A B
---------- --------------------
1 9

已用时间: 00: 00: 00.00
SQL>
__________________
I love oracle
But i hate IT

oracle文档
http://download-west.oracle.com/docs/cd/A87861_01/NT817EE/index.htm


由 rfeng 于 02-07-29 17:47 发表: 
 学习学习
让我看看你的test和test1表的desc吧，是不是要求test1表的a字段也是为主键呢？

是不是这样连接表的update和delete，还有insert操作都是需要有主键约束的呢？
__________________


……HELLO * ORACLE……


由 yangtingkun 于 02-07-29 18:00 发表: 
biti，LHLH。


由 gyang 于 02-07-29 19:04 发表: 
mark


由 biti_rainy 于 02-07-29 19:14 发表: 
有主键的要求是很正常的
这个大家应该可以理解，如果返回的是多条，逻辑上就有问题了
在嵌套查询中一般是通过 where rownum = 1 来解决的

SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
C NUMBER
D VARCHAR2(20)

SQL> desc test1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B VARCHAR2(20)

SQL> 

但是我现在遭遇一个困惑
就是目前还没有找到怎么控制删除的是表连接的哪一个表，faint
就算在两个表中分别使用主键也没有找出来
不管我怎么做，delete怎么写，它就只删除test表而不是test1表
大家帮忙看看吧
我现在不方便做大量测试


SQL> conn sys/rainy as sysdba
已连接。
SQL> alter system flush shared_pool;

系统已更改。

已用时间: 00: 00: 00.03
SQL> conn rainy/rainy
已连接。
SQL> select *from test;

A B C D
---------- ---------- ---------- --------------------
2 2 2 2
1 3 3 wew

已用时间: 00: 00: 00.00
SQL> delete (select a.a aa,a.b ab,b.a from test b,test1 a where b.a = a.a ) 
where aa =1;

已删除 1 行。

已用时间: 00: 00: 00.00
SQL> select * from test;

A B C D
---------- ---------- ---------- --------------------
2 2 2 2

已用时间: 00: 00: 00.00
SQL>
__________________
I love oracle
But i hate IT

oracle文档
http://download-west.oracle.com/docs/cd/A87861_01/NT817EE/index.htm


由 rfeng 于 02-07-29 19:44 发表: 
Re: 有主键的要求是很正常的
  quote:

  最初由 biti_rainy 发布
  这个大家应该可以理解，如果返回的是多条，逻辑上就有问题了
  在嵌套查询中一般是通过 where rownum = 1 来解决的

  SQL> desc test
  名称 是否为空? 类型
  ----------------------------------------- -------- 
----------------------------
  A NUMBER
  B NUMBER
  C NUMBER
  D VARCHAR2(20)

  SQL> desc test1
  名称 是否为空? 类型
  ----------------------------------------- -------- 
----------------------------
  A NOT NULL NUMBER
  B VARCHAR2(20)

  SQL> 

  但是我现在遭遇一个困惑
  就是目前还没有找到怎么控制删除的是表连接的哪一个表，faint
  就算在两个表中分别使用主键也没有找出来
  不管我怎么做，delete怎么写，它就只删除test表而不是test1表
  大家帮忙看看吧
  我现在不方便做大量测试


  SQL> conn sys/rainy as sysdba
  已连接。
  SQL> alter system flush shared_pool;

  系统已更改。

  已用时间: 00: 00: 00.03
  SQL> conn rainy/rainy
  已连接。
  SQL> select *from test;

  A B C D
  ---------- ---------- ---------- --------------------
  2 2 2 2
  1 3 3 wew

  已用时间: 00: 00: 00.00
  SQL> delete (select a.a aa,a.b ab,b.a from test b,test1 a where b.a = a.a ) 
  where aa =1;

  已删除 1 行。

  已用时间: 00: 00: 00.00
  SQL> select * from test;

  A B C D
  ---------- ---------- ---------- --------------------
  2 2 2 2

  已用时间: 00: 00: 00.00
  SQL> 





就刚才首个帖子上写的，是不是还是要test1表的a列不能为空呀，你看看这里：
SQL> desc test1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B VARCHAR2(20)

这样想，是不是定义你的test表中的a列not null就可以了满足你的update语句了呢？

另外，在这个语句中：
SQL> delete (select a.a aa,a.b ab,b.a from test b,test1 a where b.a = a.a ) 
where aa =1;

是不是好象就只能删除test表呢，因为后面的where子句是aa 如果要是ba 的话，就删除了test1表吧

但是我测试后有这样的错误：
ORA-01752: 不能从没有一个键值保存表的视图中删除
是不是在连接表中没有默认的主键，就不太清楚了。

对连接表的操作，各路大侠快来救急呀！！！
__________________


……HELLO * ORACLE……


由 biti_rainy 于 02-07-29 21:09 发表: 
关于主键或者是否为null
都不是我们应该关心的重点
我也不关心这些，这些都是根据具体情况应用的时候考虑的，也不是什么大问题

我现在的问题是：无论怎么写都是删除的 test表，而无法删除 test1表！
__________________
I love oracle
But i hate IT

oracle文档
http://download-west.oracle.com/docs/cd/A87861_01/NT817EE/index.htm


由 rfeng 于 02-07-29 21:46 发表: 
Re: 关于主键或者是否为null
  quote:

  最初由 biti_rainy 发布
  都不是我们应该关心的重点
  我也不关心这些，这些都是根据具体情况应用的时候考虑的，也不是什么大问题

  我现在的问题是：无论怎么写都是删除的 test表，而无法删除 test1表！ 





我觉得还是建立个触发器简单，要是找到可以删除test1表的语句，不还是要写在一个过程或是函数中么。

这就不如触发器好了哦！ ：）
__________________


……HELLO * ORACLE……


由 violet 于 02-07-29 22:34 发表: 
就是目前还没有找到怎么控制删除的是表连接的哪一个表，faint
就算在两个表中分别使用主键也没有找出来
不管我怎么做，delete怎么写，它就只删除test表而不是test1表


我怎么测试可以呀,删除有主键的那一个表.
SQL> alter table zr_test add primary key(a);

Table altered

SQL> delete (select b.a ba,b.b bb from zr_test a,zr_test1 b where a.a = b.a );

delete (select b.a ba,b.b bb from zr_test a,zr_test1 b where a.a = b.a )

ORA-01752: cannot delete from view without exactly one key-preserved table

SQL> alter table zr_test1 drop primary key;

Table altered

SQL> delete (select b.a ba,b.b bb from zr_test a,zr_test1 b where a.a = b.a );

1 row deleted

SQL> select * from zr_test1;

A B
---------- ----------

SQL> select * from zr_test;

A B C D 
---------- ---------- ---------- --------------------
1 9 1 1 
2 2 2 2 
3 3 3 wew


由 rfeng 于 02-07-29 22:37 发表: 
 这里还行不通
我这样测试的，你看还是有问题的，不如触发器方便了。

SQL> desc ww;
Name Type Nullable Default Comments 
---- ----------- -------- ------- -------- 
NAME VARCHAR2(8) 
NUM NUMBER 

SQL> desc ww1;
Name Type Nullable Default Comments 
---- ----------- -------- ------- -------- 
NAME VARCHAR2(8) 
NUM NUMBER 

SQL> select * from ww;

NAME NUM
-------- ---------------------------------------
wang 999
zhang 123434
w 2
chen 13
wang 8247
wang 9837
wang 34813

7 rows selected

SQL> select * from ww1;

NAME NUM
-------- ---------------------------------------
wang 0
chen 13
wang 8247
wang 9837
wang 34813

SQL> delete (select a.name aa,a.num ab,b.name ba,b.num bb from ww a,ww1 b where 
a.name = b.name and a.num = b.num) where ab = 9837;

delete (select a.name aa,a.num ab,b.name ba,b.num bb from ww a,ww1 b where 
a.name = b.name and a.num = b.num) where ab = 9837

ORA-01752: 不能从没有一个键值保存表的视图中删除

附注：两个表的主键都是组合主键。
__________________


……HELLO * ORACLE……


由 THUNDER2 于 02-07-29 22:47 发表: 
rainy ,我知道怎么回事啦
我建了2个表test,test1 都在a 上建立了主踺

SQL> select * from test1;
A B
---------- ----------
1 2
2 2
SQL> select * from test;
A B
---------- ----------
1 1
2 1
3 1

SQL>delete (select a.a aa,a.b ab,b.a from test b,test1 a where b.a = a.a );
ORA-01752: cannot delete from view without exactly one key-preserved table
我就奇怪明明2个表都是key-preserved table啊,再查了一下oracle错误文档:
ORA-01752 cannot delete from view without exactly one key-preserved table
Cause: The deleted table either had no key preserved tables, had more than one 
key-preserved table , or the key-preserved table was an unmerged view or a table 
from a read-only view.

恍然大悟join view 中不能同时存在多于1个key-preserved table
sql>alter table test1 drop primary key;
Table altered.
SQL> delete (select a.a aa,a.b ab,b.a from test b,test1 a where b.a = a.a );
2 rows deleted.
SQL> select * from test;
A B
---------- ----------
3 1


由 THUNDER2 于 02-07-29 22:52 发表: 
继续
SQL> rollback;

Rollback complete.

SQL> alter table test add primary key(a);

Table altered.

SQL> alter table test1 drop primary key;

Table altered.

SQL> delete (select a.a aa,a.b ab,b.a from test b,test1 a where b.a = a.a );

2 rows deleted.

SQL> select * from test;

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

SQL> select * from test1;

no rows selected

只会更新key perserved table


由 biti_rainy 于 02-07-29 23:09 发表: 
呵呵
只要大家明白就可以了

BTW：至于关于触发器的说法
我没有明白怎么跟触发器扯上关系了
我对于这种方式的提议
并不是为了炫耀sql怎么华丽
主要是考虑到大表需要更新或者删除的时候
正好又要表连接为条件
因为在sybase数据库中是有这种功能的
很多人抱怨oracle没有这种功能
于是只能通过嵌套来解决
我希望这样效率能高一点
__________________
I love oracle
But i hate IT