Delete,Update与Join

UPDATE:
UPDATE A SET ApproverID=NULL
FROM [SH_MaterialApplyBuyBill] A
LEFT JOIN [SH_MaterialApplyBuyBillDetail] B ON A.ID=B.[MaterialApplyBuyBillID]
WHERE A.id=125 AND @InDetailCount=0
DELETE:
DELETE A FROM [SH_ClosingBalance] A LEFT JOIN [SH_StoreHouse] B ON A.StoreHouseID=B.ID
WHERE B.departmentID='1000'

======================================================================

Update 

Update XXX set XXX where 这种写法大家肯定都知道，才发现update和delete居然支持inner join的update方式，这个在表间关联来做更新和删除操作非常有用.

列子:

Sql代码 < id=Player1255328313567 pluginspage=http://www.macromedia.com/go/getflashplayer src=http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 height=15 type=application/x-shockwave-flash tplayername="SWF"splayername="SWF" mediawrapchecked="true"flashvars="clipboard=update%20tb_User%0Aset%20pass%3D''%0Afrom%20tb_User%20usr%0Ainner%20join%20tb_Address%20addr%20on%20usr.nAddressFK%20%3D%20addr.nAddressID%0Awhere%20usr.id%3D123"quality="high" allowscriptaccess="always">

1. update tb_User  
2. set pass=''  
3. from tb_User usr  
4. inner join tb_Address addr on usr.nAddressFK = addr.nAddressID  
5. 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=123

update的格式是

update t1 set t1.name=’Liu’ from t1 inner join t2 on t1.id = t2.tid

MYSQL，ACCESS 写法如下:

Sql代码 < id=Player1255328313600 pluginspage=http://www.macromedia.com/go/getflashplayer src=http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 height=15 type=application/x-shockwave-flash tplayername="SWF"splayername="SWF" mediawrapchecked="true"flashvars="clipboard=UPDATE%20mem_world%20AS%20mw1%20INNER%20JOIN%20mem_world%20%20AS%20%20mw2%20%20%0AON%20%20mw1.parentid%20%3D%20mw2.wid%20%0ASET%20mw1.level%20%3D%20mw2.level%20%20%0AWHERE%20mw2.baseid%20%3D%20107%20%20%0AAND%20%20mw2.parentid%20%3D%200%20%20%0AAND%20%20mw2.size%20%3E%201%3B"quality="high" allowscriptaccess="always">

1. UPDATE mem_world AS mw1 INNER JOIN mem_world  AS  mw2    
2. ON  mw1.parentid = mw2.wid   
3. SET mw1.level = mw2.level    
4. WHERE mw2.baseid = 107    
5. AND  mw2.parentid = 0    
6. 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代码 < id=Player1255328313633 pluginspage=http://www.macromedia.com/go/getflashplayer src=http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 height=15 type=application/x-shockwave-flash tplayername="SWF"splayername="SWF" mediawrapchecked="true"flashvars="clipboard=DELETE%20mwb%20FROM%20%20mem_world_building%20AS%20mwb%20INNER%20JOIN%20mem_world%20AS%20mw%0AON%20mwb.wid%20%3D%20mw.wid%0Awhere%20mw.type%20between%2011%20and%2015%20%0Aand%20baseid%20%3D%20107%20%0Aand%20mw.parentid%20%3C%3E%200%20%0Aand%20%20mw.size%20%3E%201%3B"quality="high" allowscriptaccess="always">

1. DELETE mwb FROM  mem_world_building AS mwb INNER JOIN mem_world AS mw  
2. ON mwb.wid = mw.wid  
3. where mw.type between 11 and 15   
4. and baseid = 107   
5. and mw.parentid <> 0   
6. 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代码 < id=Player1255328313684 pluginspage=http://www.macromedia.com/go/getflashplayer src=http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 height=15 type=application/x-shockwave-flash tplayername="SWF"splayername="SWF" mediawrapchecked="true"flashvars="clipboard=DELETE%20TABLE1%20where%20KHID%20exists%20(%20select%20KHID%20from%20table2%20where%20FWDWID%3D8)"quality="high" allowscriptaccess="always">

1. DELETE TABLE1 where KHID exists ( select KHID from table2 where FWDWID=8)  

Oracle的 update (SQL) t  set t.XX =

Sql代码 < id=Player1255328313660 pluginspage=http://www.macromedia.com/go/getflashplayer src=http://nodonkey.javaeye.com/javascripts/syntaxhighlighter/clipboard_new.swfwidth=14 height=15 type=application/x-shockwave-flash tplayername="SWF"splayername="SWF" mediawrapchecked="true"flashvars="clipboard=DELETE%20TABLE1%20where%20exists%20(%20select%201%20from%20table2%20where%20and%20table1.khid%3Dtable2.khid%20and%20FWDWID%3D8)%3B%0A"quality="high" allowscriptaccess="always">

1. 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);