Updating a many to many table with SQL

Question

I have a table with animals

CREATE TABLE Animals
(
AnimalId int NOT NULL,
Color int NOT NULL,
Breed int NOT NULL,
Genre int NOT NULL,
);

And a table exactly the same but everything is optional (except the key)

CREATE TABLE Expenses
(
ExpenseId int NOT NULL,
Color int,
Breed int,
Genre int,
);

And finally a many to many table:

CREATE TABLE AnimalsExpenses
(
ExpenseId int NOT NULL FOREIGN KEY REFERENCES Expenses(ExpenseId),
AnimalId int NOT NULL FOREIGN KEY REFERENCES Animals(AnimalId),
);

The record (a, e) should be on the table AnimalsExpenses if

((SELECT Color FROM Animals WHERE AnimalId = a) = (SELECT Color FROM Expenses WHERE ExpenseId = e)
OR (NULL) = (SELECT Color FROM Expenses WHERE ExpenseId = e))
AND
((SELECT Breed FROM Animals WHERE AnimalId = a) = (SELECT Breed FROM Expenses WHERE ExpenseId = e)
OR (NULL) = (SELECT Breed FROM Expenses WHERE ExpenseId = e))
AND
((SELECT Genre FROM Animals WHERE AnimalId = a) = (SELECT Genre FROM Expenses WHERE ExpenseId = e)
OR (NULL) = (SELECT Genre FROM Expenses WHERE ExpenseId = e))

...how would it be a query that updates AnimalsExpenses ? that is: it removes the records that shouldn't be on the n-m table and it adds the ones that need to be there

Example:

------ Animals -------------------
 AnimalId  Color  Breed  Genre
----------------------------------
     1      1       1      1
     2      1       1      2
     3      1       2      2

----- Expenses -------------------
 ExpenseId  Color  Breed  Genre
----------------------------------
     1       NULL   NULL   NULL      (applies to every animal)
     2       NULL    2     NULL      (applies to animals of breed 2)
     3         1     2      2        (applies exactly to animal 3)

----- AnimalsExpenses -------------------------------------------
  AnimalId   ExpenseId   Is it ok?
-----------------------------------------------------------------
      1        1         yes, because "expense 1" applies to all
      2        1         yes, because "expense 1" applies to all
      3        1         yes, because "expense 1" applies to all

      1        2         no, breed doesnt match
      2        2         no, breed doesnt match
      3        2         yes, because "expense 2" matches breed with "animal 3"

      1        3         no, breed and genre doesnt match
      2        3         no, breed doesnt match
      3        3         yes, everything matches

Answer 1

Why don't you just truncate the table and then run

Insert into AnimalsExpenses
select a.AnimalId
  , e.ExpenseId
  from Animals a
  inner join Expenses e on a.Breed = ISNULL(e.Breed, a.Breed) 
          AND a.Color = ISNULL(e.Color, a.Color) 
          AND a.Genre = ISNULL(e.Genre, a.Genre)