Multiple Update with Multiple Conditions

Question

We are told to watch out for things being set to null (we dont want) by using the IN clause in the following:

UPDATE Tests SET
     TestScore =
              CASE
                  WHEN TestId = 10 THEN 1000
                  WHEN TestId = 11 THEN 1100
              END,
     TestScore2 =
              CASE
                  WHEN TestId = 10 THEN 2000
                  WHEN TestId = 11 THEN 2100
              END
     WHERE TestId IN (10, 11)

But what happens when there two conditions required, namely the joint combo of (TestId, TestSubId)? Ie, what do I do for the IN clause, the ???, in the following to show it has to be in the combos (10,25) and (11,22):

UPDATE Tests SET
    TestScore = CASE
        WHEN (TestId = 10 AND TestSubId = 25) THEN 1000
        WHEN (TestId = 11 AND TestSubId = 22) THEN 1100
    END,
    TestScore2 = CASE
        WHEN (TestId = 10 AND TestSubId = 25) THEN 2000
        WHEN (TestId = 11 AND TestSubId = 22) THEN 2100
    END
    WHERE TestId, TestSubId IN ?????

Answer 1

In MySQL, you can use tuple comparison:

WHERE (TestId, TestSubId) IN ((10,25), (11,22))

That looks nice and succinct, although, as ypercubeᵀᴹ mentioned in a comment, it may not work well performance-wise.

However, given how the conditions are re-used in your UPDATE statement, you could also take a different approach altogether: represent the affected IDs and the new values as a derived table and use an update with a join:

UPDATE
    Tests AS old
    INNER JOIN
    (
        SELECT 10 AS TestId, 25 AS TestSubId, 1000 AS TestScore, 2000 AS TestScore2
        UNION ALL
        SELECT 11, 22, 1100, 2100
    ) AS new
    ON old.TestId = new.TestId AND old.TestSubId = new.TestSubId
SET
    old.TestScore  = new.TestScore,
    old.TestScore2 = new.TestScore2
;

This way the new derived table acts both as a filter for Tests and a supplier of new values for the update.

Answer 2

You cannot use the in operator to filter two different columns. For this example, you would have to use the standard logical operators:

UPDATE Tests SET
TestScore = CASE
    WHEN (TestId = 10 AND TestSubId = 25) THEN 1000
    WHEN (TestId = 11 AND TestSubId = 22) THEN 1100
END,
TestScore2 = CASE
    WHEN (TestId = 10 AND TestSubId = 25) THEN 2000
    WHEN (TestId = 11 AND TestSubId = 22) THEN 2100
END
WHERE (TestId = 10 AND TestSubId = 25)
  OR (TestId = 11 AND TestSubId = 22)