6

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 ?????

2 Answers 2

7

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.

2
  • This looks really classy. Should I use this method to replace the standard case (my first blue bit of code in my OP) where I use the 'IN (10, 11)'.
    – Rewind
    Apr 17, 2016 at 20:59
  • @Rewind: I've certainly used this in SQL Server. It's somewhat inconvenient that MySQL doesn't let you supply column aliases after the table alias, for derived tables, as in ... FROM (SELECT 10, 25, 1000, 2000 UNION ALL SELECT 11, 22, 1100, 2100) AS new (TestId, TestSubId, TestScore, TestScore2) – IMHO, that would have made the query look a little more elegant. Nevertheless, I would still use the technique despite that limitation.
    – Andriy M
    Apr 17, 2016 at 21:31
4

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)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.