update和delete支持inner join的update方式

迈不过友情╰ 2021-11-01 09:48 322阅读 0赞

update和delete居然支持inner join的update方式，这个在表间关联来做更新和删除操作非常有用.   
  
列子:   
  
  
  
  
Sql代码   
update tb\_User     
set pass=''    
from tb\_User usr     
inner join tb\_Address addr on usr.nAddressFK = addr.nAddressID     
where usr.id=123    
  
update tb\_User   
set pass=''   
from tb\_User usr   
inner join tb\_Address addr on usr.nAddressFK = addr.nAddressID   
where usr.id=123update的格式是   
  
update t1 set t1.name=’Liu’ from t1 inner join t2 on t1.id = t2.tid   
  
  
  
  
MYSQL，ACCESS 写法如下:   
  
  
  
  
Sql代码   
UPDATE mem\_world AS mw1 INNER JOIN mem\_world  AS  mw2       
ON  mw1.parentid = mw2.wid      
SET mw1.level = mw2.level       
WHERE mw2.baseid = 107       
AND  mw2.parentid = 0       
AND  mw2.size > 1;    
  
UPDATE mem\_world AS mw1 INNER JOIN mem\_world  AS  mw2    
ON  mw1.parentid = mw2.wid   
SET mw1.level = mw2.level    
WHERE mw2.baseid = 107    
AND  mw2.parentid = 0    
AND  mw2.size > 1;   
  
on是表连接的筛选条件   
就是说，表连接后，会产生一个类似于临时的视图这么一个东西   
where是从这个临时的视图中筛选数据的   
所以，你首先要搞清，你的所谓的2个条件属于哪一种   
  
  
  
  
Delete   
  
  
  
  
delete 语句也是类似   
  
delete from t1 from t1 inner join t2 on t1.id = t2.tid   
  
注意蓝色部分。   
  
mysql:   
  
  
Sql代码   
DELETE mwb FROM  mem\_world\_building AS mwb INNER JOIN mem\_world AS mw     
ON mwb.wid = mw.wid     
where mw.type between 11 and 15      
and baseid = 107      
and mw.parentid <> 0      
and  mw.size > 1;    
  
DELETE mwb FROM  mem\_world\_building AS mwb INNER JOIN mem\_world AS mw   
ON mwb.wid = mw.wid   
where mw.type between 11 and 15   
and baseid = 107   
and mw.parentid <> 0   
and  mw.size > 1;    
下面是ORACLE的:   
  
  
Sql代码   
DELETE TABLE1 where exists ( select 1 from table2 where and table1.khid=table2.khid and FWDWID=8);    
  
DELETE TABLE1 where exists ( select 1 from table2 where and table1.khid=table2.khid and FWDWID=8);   
  
  
  
Sql代码   
DELETE TABLE1 where KHID exists ( select KHID from table2 where FWDWID=8)    
  
DELETE TABLE1 where KHID exists ( select KHID from table2 where FWDWID=8)

\--转自网络

转载于:https://www.cnblogs.com/AppleZhang/p/5754241.html