1) 两表(多表)关联update -- 仅在where字句中的连接
SQL 代码
- --这次提取的数据都是VIP,且包括新增的,所以顺便更新客户类别
- update customers a -- 使用别名
- set customer_type='01' --01 为vip,00为普通
- where exists (select 1
- from tmp_cust_city b
- where b.customer_id=a.customer_id
- )
2) 两表(多表)关联update -- 被修改值由另一个表运算而来
SQL 代码
- update customers a -- 使用别名
- set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
- where exists (select 1
- from tmp_cust_city b
- where b.customer_id=a.customer_id
- )
- -- update 超过2个值
- update customers a -- 使用别名
- set (city_name,customer_type)=(select b.city_name,b.customer_type
- from tmp_cust_city b
- where b.customer_id=a.customer_id)
- where exists (select 1
- from tmp_cust_city b
- where b.customer_id=a.customer_id
- )
注意在这个语句中,
=(select b.city_name,b.customer_type from tmp_cust_city b
where b.customer_id=a.customer_id )
与
(select 1 from tmp_cust_city b
where b.customer_id=a.customer_id)
是两个独立的子查询,查看执行计划可知,对b表/索引扫描了2篇;
=(select b.city_name,b.customer_type from tmp_cust_city b
where b.customer_id=a.customer_id )
与
(select 1 from tmp_cust_city b
where b.customer_id=a.customer_id)
是两个独立的子查询,查看执行计划可知,对b表/索引扫描了2篇;
3) 在oracle的
update
语句语法中,除了可以
update
表之外,也可以是视图,所以有以下
1
个特例:
SQL 代码
- update (select a.city_name,b.city_name as new_name
- from customers a,
- tmp_cust_city b
- where b.customer_id=a.customer_id
- )
二、oracle多表关联删除的两种方法
第一种使用exists方法
- delete
- from tableA
- where exits
- (
- select 1
- from tableB
- Where tableA.id = tableB.id
- )
- delete
- from
- (
- select 1
- from tableA,TableB
- where tableA.id = tableB.id
- )