使用事务块、表变量和JOIN删除多个表数据。

BEGIN TRANSACTION;

   declare @deletedIds table ( id int );
   
   DELETE w
   output deleted.EmployeeRun into @deletedIds
   FROM WorkRecord2 w
   INNER JOIN Employee e
           ON e.EmployeeNo = w.EmployeeRun
          AND w.Company = 1
          AND w.date = '2013-05-06';

   DELETE e
   FROM Employee as e
   INNER JOIN @deletedIds as d
           ON d.id = e.EmployeeNo;
COMMIT TRANSACTION;

请从url检查https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=43330dda6f1b71b8ec4172a24d5b6921

使用临时表和JOIN删除多个表数据。删除后删除临时表。

BEGIN TRANSACTION;

    -- create temporary table
    create table #deletedRecords (employeeId int);
    
    -- INSERT INTO #deletedRecords
    SELECT e.EmployeeNo
    FROM WorkRecord2 w
    INNER JOIN Employee e
           ON e.EmployeeNo = w.EmployeeRun
          AND w.Company = 1
          AND w.date = '2013-05-06';
          
    -- delete from WorkRecord2
    DELETE w
    FROM WorkRecord2 w
    INNER JOIN #deletedRecords d
           ON w.EmployeeRun = d.employeeId;
           
    -- delete from Employee using exists
    DELETE 
    FROM Employee
    WHERE EXISTS (SELECT 1
                  FROM #deletedRecords d
                  WHERE d.employeeId = EmployeeNo);
                  
    -- drop temporary table
    DROP TABLE #deletedRecords;

COMMIT TRANSACTION;

请从url检查https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=d52c6c1ed91669d68fcc6bc91cb32d78

使用SELECT INTO创建临时表的另一种方法

BEGIN TRANSACTION;

    SELECT  e.EmployeeNo employeeId 
    INTO #deletedRecords
    FROM WorkRecord2 w
    INNER JOIN Employee e
           ON e.EmployeeNo = w.EmployeeRun
          AND w.Company = 1
          AND w.date = '2013-05-06';
          
    -- delete from WorkRecord2
    DELETE w
    FROM WorkRecord2 w
    INNER JOIN #deletedRecords d
           ON w.EmployeeRun = d.employeeId;
           
    -- delete from Employee using exists
    DELETE 
    FROM Employee
    WHERE EXISTS (SELECT 1
                  FROM #deletedRecords d
                  WHERE d.employeeId = EmployeeNo);
                  
    -- drop temporary table
    DROP TABLE #deletedRecords;

COMMIT TRANSACTION;

请从url检查https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=0f02f05616ce5b4dcc8fc67c6cf1e640

使用JOIN删除单个表数据

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

请从url检查https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=84a60d1368556a8837281df36579334a

使用CTE删除单表数据

WITH cte AS (
     SELECT w.EmployeeRun
     FROM WorkRecord2 w
     WHERE EXISTS (SELECT 1
                   FROM Employee 
                   WHERE EmployeeNo = w.EmployeeRun)
         AND w.Company = 1
         AND w.date = '2013-05-06'
)
DELETE
FROM cte

请从url检查https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=6590007b3c8c2ffad5563bd86606c5b1

在子表中创建外键时使用ON CASCADE DELETE。如果删除父表数据，则会自动删除相应的子表数据。

另一种方法是使用CTE：

;WITH cte
     AS (SELECT *
         FROM   workrecord2 w
         WHERE  EXISTS (SELECT 1
                        FROM   employee e
                        WHERE  employeerun = employeeno
                               AND company = '1'
                               AND date = '2013-05-06'))
DELETE FROM cte

注意：当您想要删除时，我们不能在CTE中使用JOIN。

试试看：

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

在SQL Server Management Studio中，我可以轻松创建SELECT查询：

SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.Postcode
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf

我可以执行它，我的所有联系人都会显示出来。

现在将SELECT更改为DELETE：

DELETE Contact
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf

您在SELECT语句中看到的所有记录都将被删除。

您甚至可以使用相同的过程创建更困难的内部联接，例如：

DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf

这可能对你有帮助-

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

只需在DELETE和FROM之间添加要删除记录的表的名称，因为我们必须指定要删除的表。还要删除ORDERBY子句，因为删除记录时没有要排序的内容。

因此，您的最终查询应该如下所示：

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