CODEWITHSUNDEEP
sqlsql-serverentity-frameworkado.net
Jonathan Wood
Jonathan Wood

Reputation: 67283

Updating Multiple Many-to-Many Relationships

I have two tables, Cars and Drivers, that are joined by a third table, CarDrivers, in a many-to-many relationship.

My UI allows the user to check any number of drivers associated with the current car. Each check indicates that a row should be entered into the CarDrivers table.

My question is: what's the most efficient way to update those rows when the user submits the form?

I need to go through and add a row into CarDrivers for each item that was checked and delete one for each item that was unchecked, while leaving those that have not changed.

The only way I see is to go through each combination, one at a time, and add those that don't already exist or remove those that need to be removed. Is there a slicker way?

I can use Entity Frameworks 4, ADO.NET, straight SQL queries or stored procedures.

Upvotes: 5

Views: 3359

Answers (2)

onedaywhen
onedaywhen

Reputation: 57063

This is exactly the scenario the MERGE syntax was invented to handle, notably the WHEN NOT MATCHED BY SOURCE clause.

Broadly speaking, put the current state values into a staging table then using MERGE to handle the INSERT and DELETE actions in one hit.

Here's a brief sketch:

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;

Upvotes: 5

imm
imm

Reputation: 5919

I doubt this is more efficient, but "slicker", maybe, particularly if you don't have a huge amount of data. When a user submits a form that updates the cars-drivers relationships, why not first delete ALL relationships in CarDrivers related to them and then insert just the ones they checked? Alternatively, you could have a uniq constraint on both columns in CarDrivers, and then just worry about inserting and deleting, rather than checking for existing records in your code.

Upvotes: 2

Related Questions