0

Suppose I have a table such as:

CREATE TABLE IF NOT EXISTS temporary
(
    id INT NOT NULL,
    field1 INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now lets say I want to update the rows with ids 1 2 3 with data 100 200 300 respectively. Is it possible to achieve that with one query. (In this particular case I know its 3 ids so I can just use case "hard-coded in the query", but in reaility I wont know if there 1, 10, or 20 ids therefore I wont be able to use case.

The only possible solution I have come to, is to make the query in my code; eg: 

var PASSED_QUERY = 
"tmp.field1 =
 (CASE 
     WHEN id=1 THEN field1=100,
     WHEN id=2 THEN field1=200,
     WHEN id=3 THEN field1=300
     ELSE field1 
 END)";

var ARRAY = [1,2,3];

and pass it to Mysql like UPDATE temporary tmp SET PASSED_QUERY WHERE tmp.id IN(ARRAY);

Is there a better way to go about this?

(In this example I have omitted some nodejs syntax to make it more understandable since they did not contribute to the solution.)

John James
  • 587
  • 3
  • 8
  • 19
  • Two good answers here: https://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query – clay Jan 25 '18 at 18:57
  • Possible duplicate of [MySQL - UPDATE multiple rows with different values in one query](https://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query) – clay Jan 25 '18 at 18:58
  • In that post they do it exactly as I have shown above with the only difference that the cases are "hard-coded" in MySQL where I create it in my code. Am not saying using 20 case like that is bad, am just saying its the same thing as I have done. – John James Jan 25 '18 at 19:00
  • 1
    Since you're using MySQL, check out the second answer. https://stackoverflow.com/a/34866431/3018068. A different approach without all the cases. – clay Jan 25 '18 at 19:17

0 Answers0