SQL: Limit on CASE (number of WHEN, THEN conditions)
mysql, oracle, sql
Solution
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.
Problem
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])` ```