简单的例子:
update table11 a set a.name1 = (select b.name2 from table22 b where a.id1 = b.id2)
where exists (select 1 from table22 b where b.id2 = a.id1);
commit;
或者写成:
update table11 a set a.name1 = (select b.name2 from table22 b where a.id1 = b.id2)
where exists (select * from table22 b where b.id2 = a.id1);
注意:1和*是一样的
或者写成:
update table11 a set a.name1 = (select b.name2 from table22 b where a.id1 = b.id2)
where a.id1 in (select b.id2 from table22 b);
exists这里起的作用是与in是一样的,但是效率会比in好
复杂的例子:
update pub_banktype h
set (h.banktypename) = ( select lastTable.itemnamefrom (select b.*, mytable.itemname
from pub_banktype b,
(select d.*
from pub_dditem d,
pub_datadictionary t
where d.ddno = t.ddno
and t.ddcode = 'BANKTYPE') mytable
where mytable.itemcode = b.banktypecode
and mytable.itemname != b.banktypename) lastTable
where lastTable.banktypeno = h.banktypeno)
where exists (select 1
from (select b.*, mytable.itemname
from pub_banktype b,
(select d.*
from pub_dditem d,
pub_datadictionary t
where d.ddno = t.ddno
and t.ddcode = 'BANKTYPE') mytable
where mytable.itemcode = b.banktypecode
and mytable.itemname != b.banktypename) lastTable
where lastTable.banktypeno = h.banktypeno);
commit;
若干个由于限定提交查询出的结果可以作为临时表,避免查询条件复杂
关键点:
临时表myTable:
(select d.*
from pub_dditem d,
pub_datadictionary t
where d.ddno = t.ddno
and t.ddcode = 'BANKTYPE') mytable
临时表lastTable:
(select b.*, mytable.itemname
from pub_banktype b,
(select d.*
from pub_dditem d,
pub_datadictionary t
where d.ddno = t.ddno
and t.ddcode = 'BANKTYPE') mytable
where mytable.itemcode = b.banktypecode
and mytable.itemname != b.banktypename) lastTable
为了看得更仔细,我们把mytable前面的定义 省略掉得到:
update pub_banktype h
set (h.banktypename) = ( select lastTable.itemnamefrom (select b.*, mytable.itemname
from pub_banktype b, mytable
where mytable.itemcode = b.banktypecode
and mytable.itemname != b.banktypename) lastTable
where lastTable.banktypeno = h.banktypeno)
where exists (select 1
from (select b.*, mytable.itemname
from pub_banktype b,mytable
where mytable.itemcode = b.banktypecode
and mytable.itemname != b.banktypename) lastTable
where lastTable.banktypeno = h.banktypeno);
再做一次简化,将lastTable的定义语句去掉得到:
update pub_banktype h
set (h.banktypename) = ( select lastTable.itemnamefrom lastTable
where lastTable.banktypeno = h.banktypeno)
where exists (select 1
from lastTable
where lastTable.banktypeno = h.banktypeno);
现在看上去是不是和下面简单的例子一样呢:
update table11 a set a.name1 = (select b.name2 from table22 b where a.id1 = b.id2)
where exists (select 1 from table22 b where b.id2 = a.id1);
其实这就是多表联合更新的关键----------临时表。