[SQL Server]SQL Server数据库中从一张表的条件删除另张表数据的方法有哪些?

SQL Server 作者: Rector 264阅读 0评论 0收藏 收藏本文

郑重申明:本文未经许可,禁止任何形式转载

[SQL Server]SQL Server数据库中从一张表的条件删除另张表数据的方法有哪些?

问题描述

比如有类似如下的删除语句:

DELETE FROM WorkRecord2 
INNER JOIN Employee ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

注:以上语句是错误的

方案一

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

方案二

DELETE WorkRecord2 
      FROM WorkRecord2 
INNER JOIN Employee 
        ON EmployeeRun=EmployeeNo
     WHERE Company = '1' 
       AND Date = '2013-05-06';

方案三

DELETE FROM WorkRecord2 
       FROM Employee 
Where EmployeeRun=EmployeeNo
      And Company = '1' 
      AND Date = '2013-05-06'

方案四

DELETE FROM dbo.WorkRecord2 
WHERE EmployeeRun IN (
    SELECT e.EmployeeNo
    FROM dbo.Employee e
    WHERE ...
)

或者

DELETE FROM dbo.WorkRecord2 
WHERE EXISTS(
    SELECT 1
    FROM dbo.Employee e
    WHERE EmployeeRun = e.EmployeeNo
        AND ....
)

方案五

QL Server 2008及以上版本,可以使用 MERGE 关键字:

MERGE WorkRecord2 T
   USING Employee S
      ON T.EmployeeRun = S.EmployeeNo
         AND Company = '1'
         AND Date = '2013-05-06'
WHEN MATCHED THEN DELETE;

阅读了该文章的人还浏览了...

本文永久链接码友网 » [SQL Server]SQL Server数据库中从一张表的条件删除另张表数据的方法有哪些?

发布于: 2018-01-10 09:31:59
分享扩散:

文章评论

获取验证码