SQL: CASE constraint (number of conditions WHEN, THEN)

Question

Consider the query (it runs for 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 become quite large, so I was wondering what is the limit on the number of conditions (WHEN, THEN) that a single query can contain. Is there any way around this?

For example:
I know that the maximum number of values ​​that can be passed in INis 1000, and to overcome this we can do

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

Answer 1

The docs for 10gR2 say:

CASE - 255. , CASE ELSE. ... THEN . , CASE, return_expr CASE .

Answer 2

:

id   value

1446  423
2372  426 
…

.

Oracle:

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

( helper.id a PRIMARY KEY )

MySQL:

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

Answer 3

, , :

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

:

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

.

Answer 4

In MySQL, you are limited only by request byte size. This is controlled by the "max_allowed_packet" parameter for your server. I can not talk about Oracle.