在 SQL Server 中使用 INNER JOIN 进行删除操作的方法

技术背景

在 SQL Server 数据库操作中，有时需要根据表之间的关联关系删除特定的数据。使用 INNER JOIN 可以将多个表进行关联，从而筛选出需要删除的数据。

实现步骤

单表删除（使用别名）

在 DELETE 和 FROM 之间添加要删除记录的表名，并指定表的别名，同时移除 ORDER BY 子句。示例代码如下：

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

单表删除（不使用别名）

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

使用子查询删除

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

使用 EXISTS 子句删除

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

多表删除（使用事务、表变量和 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;

多表删除（使用临时表和 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;

使用 CTE 删除

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

使用 MERGE 删除

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

最佳实践

• 使用事务：在进行多表删除操作时，使用事务可以确保数据的一致性。如果操作过程中出现错误，可以回滚事务，避免数据不一致的问题。
• 测试查询：在执行删除操作之前，先使用 SELECT 语句测试查询条件，确保筛选出的数据是你想要删除的数据。
• 备份数据：在进行重要的删除操作之前，建议备份相关数据，以防误操作导致数据丢失。

常见问题

• JOIN 条件错误：确保 JOIN 条件正确，否则可能会删除错误的数据。
• 权限问题：确保你有足够的权限执行删除操作。
• 数据依赖问题：在删除数据时，要考虑数据之间的依赖关系，避免删除重要的数据导致其他业务逻辑出错。例如，可以使用 ON CASCADE DELETE 来处理外键关联的数据删除。