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

In MySQL you are limited solely by the byte size of the query. This is controlled by the "max_allowed_packet" setting for your server. I cannot speak for Oracle.

Answer 2

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 3

If you have a lot of case statements like this, you should move them into a table to simplify things:

NewTable
table1_id  | result
-----------+---------
1446       | 423
2372       | 426
...        | ...

Then join on it in your query:

UPDATE table1
SET something_id = NewTable.result
INNER JOIN NewTable
ON table1.table1_id = NewTable.table1_id

It's a lot simpler.

Answer 4

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.