-3

My Table is in the following format in MySQL:

CREATE TABLE table11 (id int,col1 float,col2 int);

INSERT INTO table11 (id,col1,col2)
VALUES
(1, 1.1, 1),
(2, 1.2, 1),
(3, 1.3, 1),
(4, 1.4, 1);

I want to update the values in col2 with following values (1,2,3,4)

I wrote the following query:

UPDATE table11
SET col2 = CASE col2 
                 WHEN 1 THEN 1
                 WHEN 1 THEN 2
                 when 1 THEN 3
                 WHEN 1 THEN 4
                 ELSE col2
                 END
                 WHERE id in (1,2,3,4);  

But it is not working - output values of col2 are not changing.

  • 1
    Your query replace each 1 in the col2 to the 1 in the first matched WHEN condition. Try to replace to the (5,6,7,8) and see - all your 1s turns into 5s. – Kondybas Jul 24 '18 at 6:54
  • @Kondybas yeah it replacing with first values ..how to solve my problem. – dondapati Jul 24 '18 at 7:04
  • Do you want to enumerate records in a table with the id (or col1) ascending order? – Akina Jul 24 '18 at 8:11
  • @Akina Yes enumerate the records with ascending order. – dondapati Jul 24 '18 at 8:14
  • 1
    with ascending order There is NO ascending order! There exists ascending order while sorting by some field, fields combination or calculated expression. The best choice when each record is unique by sorting expression. – Akina Jul 24 '18 at 8:17
1

If you want to assign some values to the col2 according to the id you have to create the auxiliary table:

+----+-------+
| id | value |
+----+-------+
|  1 |     2 |
|  2 |     5 |
|  3 |     3 |
|  4 |     7 |
| .. | ..... |

Then you have to UPDATE by JOIN..ON like that:

UPDATE table11  AS w
  JOIN auxtable AS z ON z.id = w.id
   SET w.col2 = z.value
;

It is crucial to have the index(id) for the auxiliary table. Otherwise query will fall into the filesort that can be VERY slow on big tables.

This is the universal solution for the mass updating. If you heed to update only few rows you can use the next query:

UPDATE table11 
   SET col2 = CASE
                 WHEN id=1 THEN 2
                 WHEN id=2 THEN 5
                 WHEN id=3 THEN 3
                 WHEN id=4 THEN 7
                 ELSE col2
              END
 WHERE id IN (1,2,3,4)
;

As suggested by ypercube the clause WHERE id IN (...) is mandatory. If absent the query will drill through the whole table in search for the matching ids.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.