UPDATE:
UPDATE A SET ApproverID=NULL,ApproveDate=NULL,ApproveResult=100,CheckerID=null,CheckDate=null, IsCheck=0
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 A SET ApproverID=NULL,ApproveDate=NULL,ApproveResult=100,CheckerID=null,CheckDate=null, IsCheck=0
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'
如题:
前段时间程序中修改、删除sql时,涉及处理两个表。最近了解到,mysql可以直接联表操作:
联表修改:
update t1,t2 set t1.c1=* ,t2.c2=* where t1.c3=t2.c3 and t1.c2=*;
例如:修改纸条状态
update relation , information set relation.status = 2 where relation.infoid=information.infoid and information.group = 0 and relation.id in (125,126,127,128) and relation.owneruid = 1 and relation.type = 1
这样就可以直接修改符合条件的两个表的字段了。
联表删除:
delete t1,t2 from t1 left join t2 on t1.c3=t2.c3 where t1.c2=*;
例如:删除通知:
DELETE relation,
information FROM relation LEFT JOIN information ON relation.infoid = information.infoid WHERE information.group =0 AND relation.id IN ( 19 ,20,21 ) AND relation.owneruid =1
这样就可以一下删除两个表的相关记录了。
另外遍历整个数据表时,有一个比select更快的方法就是handler。大致用法如下:
handler 表名 open;
handler 表名 read next limit n;
handler 表名 close;
例如:遍历delinfoid表
mysql> handler delinfoid open;
mysql> handler delinfoid read next limit 10;
mysql> handler delinfoid read next limit 10;
mysql> handler delinfoid close;
在read时,如果不加limit的话,默认一次只读一条。
以上是使用这些方法的一些大致介绍,具体的大家可以查看手册 or google一下。
作者:jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美,在于尽态极妍 | 应用之美,在于药到病除
地址:https://jackxiang.com/post/1345/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明!
最后编辑: jackxiang 编辑于2008-10-31 18:42
评论列表