oracle l连接查询分类,ORACLE 通过连接查询更新 update select

SQL> select * from wwm2;        --要更新的表 , 多一点.

TOWN                         ID

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

222                         222

111                         111

ww'jj                       111

llll                       1111

dddd                       2222

lllldf                      111

lllldf                      111

dsafdf                      111

3435                        111

ljjjjj                      222

dsafdf                      111

TOWN                         ID

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

3435                        111

ljjjjj                      222

SQL> select * from wwm5;            --更新的条件表 , 少一点

TOWN                         ID

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

lllldf                      111

test                       9984

SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id

2  /

TOWN                         ID

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

111                         111

ww'jj                       111

lllldf                      111

lllldf                      111

dsafdf                      111

3435                        111

dsafdf                      111

3435                        111

8 rows selected.

所以,每次需要更新8条数据就是正确的. 这一步是验证更新是不是对, 错的关键.!!!

相信程序员是通过以下类似的SQL更新的,这是错误的,因为没有加WHERE

SQL>  update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id)

2  /

13 rows updated.

SQL> select * from wwm2;

TOWN                         ID

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

222

lllldf                      111

lllldf                      111

1111

2222

lllldf                      111

lllldf                      111

lllldf                      111

lllldf                      111

222

lllldf                      111

TOWN                         ID

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

lllldf                      111

222

13 rows selected.

可以看到13条记录被更新,符合条件的更新正确,不符合条件的也更新为NULL.以下是正确的方法

方法一:

SQL> update wwm2

2  set town=(select town from wwm5 where wwm5.id=wwm2.id)

3  where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id)

4  /

8 rows updated.

方法二:    与方法一道理相同,这里需要掌握EXIST的相关用法.

SQL> update wwm2

set town=(select town from wwm5 where wwm5.id=wwm2.id)

where exists (select 1 from wwm5 where wwm5.id=wwm2.id)

8 rows updated.

方法三:

SQL> update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id)

2  set atown=btown

3  /

set atown=btown

*

ERROR at line 2:

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

1* alter table wwm5 add primary key (id)

SQL> /

Table altered.

1  update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) -- 表连接之后, 更新视图的方式很好理解. 但是需要primary key.

2*  set atown=btown

SQL> /

8 rows updated.

这种方法的局限性就是需要PRIMARY 的支持.! 需要 更新被更新表有主键, 连接栏位是不是需要呢>?

方法四: --万金油. 但是数据库顾问说效能没有使用表连接好.

1  declare

2  cursor cur_wwm is select town,id from wwm5; -- 驱动 表

3  begin

4     for my_wwm in cur_wwm loop

5     update wwm2 set town=my_wwm.town -- 被驱动表.

6     where id=my_wwm.id;

7     end loop;

8* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select * from wwm2;

TOWN                         ID

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

222                         222

lllldf                      111

lllldf                      111

llll                       1111

dddd                       2222

lllldf                      111

lllldf                      111

lllldf                      111

lllldf                      111

ljjjjj                      222

lllldf                      111

TOWN                         ID

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

lllldf                      111

ljjjjj                      222

这个方法是最灵活的了.

方法五:

注意,方法五只能适用于WWM5是WWM2的子集的时候. (新特性 merge Into,哎呀! 不想学.....)

1   merge into wwm2

2   using (select town,id from wwm5) b

3   on (wwm2.id=b.id)

4   when matched then update set town=b.town

5* when not matched then insert (town,id) values (null,null)

SQL> /

9 rows merged.

SQL> select * from wwm2;

TOWN                         ID

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

---注意这个地方,被插入了一个空值.因为WWM5的ID=9984在WWM2中不能匹配,根本原因是ORACLE9必须有WHEN NOT MATCHED子句,但是ORACLE10可以不许要,也就是ORACLE10可以不写WHEN NOT MATCHED ,就不必插入NULL值了,为解决这个问题,下一步会DELETE WWM5的ID=9984,这样一来就不会执行WHEN NOT MATCHED

222                         222

lllldf                      111

lllldf                      111

llll                       1111

dddd                       2222

lllldf                      111

lllldf                      111

lllldf                      111

lllldf                      111

ljjjjj                      222

TOWN                         ID

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

lllldf                      111

lllldf                      111

ljjjjj                      222

14 rows selected.

SQL> delete from wwm5 where id=9984;

1 row deleted.

SQL>  1   merge into wwm2

SQL>   2   using (select town,id from wwm5) b

SQL>   3   on (wwm2.id=b.id)

SQL>   4   when matched then update set town=b.town

SQL>   5* when not matched then insert (town,id) values (null,null)

SQL> /

8 rows merged.

以上就是5种关连更新的例子了,希望能给开发人员解惑.

说明：如果select 子句可以返回多行记录，但返回适合where条件的记录只能是唯一的，否则将会报返回单行的select子句返回多行的错误，因为update只能跟据此处的where子句(内层where)进行相应记录的匹配更新，一次只能是一条。