zsx's Old Blog

黑历史逐步隐藏中_(:з」∠)_仅保留部分可能有些价值的文章,并不保证将来的可访问性。

Access\MSSQL的多表联立update\delete

Access:


UPDATE TABLE1 INNER JOIN [TABLE2] ON (XXXX) SET [FIELD1]=XXXX
DELETE TABLE1.* from TABLE1 INNER JOIN [TABLE2] ON (XXXXX)


MSSQL:

UPDATE TABLE1 SET [FIELD1]=XXXX FROM TABLE1 INNER JOIN TABLE2 ON (XXXXX)
DELETE TABLE1 FROM TABLE1 INNER JOIN [TABLE2] ON (XXXXX)


举例:

duoshuo.api.spam=function(meta_json){
    if(!ZC_MSSQL_ENABLE){
        objConn.Execute("UPDATE blog_Comment INNER JOIN [blog_plugin_duoshuo] ON (((blog_plugin_duoshuo.ds_cmtid)=([blog_Comment].[comm_ID]) And (blog_plugin_duoshuo.ds_key) In("+duoshuo.join2({array:meta_json.meta,before:"'",after:"'",splittag:","})+") )) SET comm_IsCheck=TRUE");
    }
    else{
        objConn.Execute("UPDATE blog_Comment SET comm_IsCheck=1 FROM blog_comment INNER JOIN [blog_plugin_duoshuo] ON (((blog_plugin_duoshuo.ds_cmtid)=([blog_Comment].[comm_ID]) And (blog_plugin_duoshuo.ds_key) In("+duoshuo.join2({array:meta_json.meta,before:"'",after:"'",splittag:","})+") )) ");
    }
    return meta_json.log_id;
}
duoshuo.api.deletepost=function(meta_json){
    objConn.Execute("DELETE blog_Comment"+(ZC_MSSQL_ENABLE?"":".*")+" FROM blog_Comment INNER JOIN [blog_plugin_duoshuo] ON  (blog_plugin_duoshuo.ds_cmtid=[blog_Comment].[comm_ID] and blog_plugin_duoshuo.ds_key in("+duoshuo.join2({array:meta_json.meta,before:"'",after:"'",splittag:","})+")) ");
    objConn.Execute("DELETE FROM blog_plugin_duoshuo WHERE ds_key in("+duoshuo.join2({array:meta_json.meta,before:"'",after:"'",splittag:","})+") ");
  
    return meta_json.log_id;
}


需要注意的是,Access里的删除在Microsoft Office Access 2013(其他版本未测试)中提示“无法删除指定的数据表”,即使加上DISTINCTROW也无济于事。但是在ASP直接使用objConn.Execute是可以的。

控制面板
您好,欢迎到访网站!
  查看权限
Google Adsense
文章归档
站点信息
  • 文章总数:259
  • 页面总数:10
  • 分类总数:17
  • 标签总数:136
  • 评论总数:1644
  • 浏览总数:216659

Powered By Z-BlogPHP 1.5.1 Zero

闽ICP备15006942号   闽公网安备 35010302000147号