sql - 更新多个多对多关系

Question

我有两个表“Cars”和“Drivers”，它们与第三个表“CarDrivers”以多对多关系连接。

我的 UI 允许用户检查与当前汽车关联的任意数量的驾驶员。每项检查都表明应将一行输入到 CarDrivers 表中。

我的问题是:当用户提交表单时更新这些行的最有效方法是什么？

我需要遍历并为每个已选中的项目在 CarDrivers 中添加一行，并为每个未选中的项目删除一行，同时保留那些未更改的项目。

我看到的唯一方法是一次一个地检查每个组合，然后添加那些不存在的组合或删除那些需要删除的组合。有没有更灵活的方法？

我可以使用 Entity Framework 4、ADO.NET、直接 SQL 查询或存储过程。

Answer 1

这正是 MERGE 的场景发明语法是为了处理，特别是 WHEN NOT MATCHED BY SOURCE 子句。

一般来说，将当前状态值放入临时表中，然后使用 MERGE 一次处理 INSERT 和 DELETE 操作。

这是一个简短的草图:

CREATE TABLE Cars (VIN INTEGER NOT NULL UNIQUE);

CREATE TABLE Drivers (driver_licence_number INTEGER NOT NULL UNIQUE);

CREATE TABLE CarDrivers 
(
 VIN INTEGER NOT NULL REFERENCES Cars (VIN), 
 driver_licence_number INTEGER NOT NULL 
    REFERENCES Drivers (driver_licence_number)
);

INSERT INTO Cars VALUES (1), (2), (3);
INSERT INTO Drivers VALUES (22), (55), (99);

INSERT INTO CarDrivers VALUES (1, 22), (1, 55);

CREATE TABLE CarDrivers_staging
(
 VIN INTEGER NOT NULL REFERENCES Cars (VIN), 
 driver_licence_number INTEGER NOT NULL 
    REFERENCES Drivers (driver_licence_number)
);

INSERT INTO CarDrivers_staging 
   VALUES (1, 55), -- remains
          (1, 99); -- insert
                   -- DELETE (1, 22)

MERGE INTO CarDrivers
   USING CarDrivers_staging S 
      ON S.VIN = CarDrivers.VIN
         AND S.driver_licence_number = CarDrivers.driver_licence_number 
WHEN NOT MATCHED THEN
   INSERT (VIN, driver_licence_number)
      VALUES (VIN, driver_licence_number)
WHEN NOT MATCHED BY SOURCE THEN
   DELETE;