SQL: Limit on CASE (number of WHEN, THEN conditions)

Question

Consider the query (it runs on both Oracle and MySQL)

UPDATE table1
SET something_id = CASE 
  WHEN table1_id = 1446 THEN 423 
  WHEN table1_id = 2372 THEN 426 
  WHEN table1_id = 2402 THEN 428 
  WHEN table1_id = 2637 THEN 429 
  WHEN table1_id = 2859 THEN 430 
  WHEN table1_id = 3659 THEN 433 
END 
WHERE table1_id IN (1446,2372,2402,2637,2859,3659)

This query can get quite large, so I was wondering what is the limit on the number of conditions (WHEN, THEN statements) a single query can hold. Is there a way around it?

For example:
I know that the max number of values that can be passed to IN is 1000 and to overcome this we can do

`WHERE TABLE1_ID IN ([1000 values]) OR TABLE1_ID IN ([more values])`

Answer 1

Put your correspondences into a helper table:

id   value

1446  423
2372  426 
…

and join.

In Oracle:

UPDATE  (
        SELECT  something_id, value
        FROM    table1
        JOIN    helper
        ON      table1.table1_id = helper.id
        )
SET     something_id = value

(don't forget to make helper.id a PRIMARY KEY for this to work)

In MySQL:

UPDATE  table1
JOIN    helper
ON      table1.table1 = helper.id
SET     table1.something_id = value

Answer 2

The docs for 10gR2 say:

The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.