How to update multiple rows in MySQL by one line?

Question

I have PHP array with data. Now, i'm using a cycle like this:

$updArray = array(1=>11,2=>12,3=>13);
foreach($updArray as $id=>$value)
{
    $query = "UPDATE table1 SET column1='$value' WHERE cart_id = '$id'";
    mysql_query($query,$link);
}

I think, if array contains thousands rows, it would be slow. Is there a way to update MySQL table (apply my data from array) without cycle and updating every row?

Possible duplicate of [MYSQL - UPDATE multiple rows with different values in one query](http://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query) — julienc, Jul 09 '16 at 16:49
Yes. For small datasets you might use IN(). For larger you might store the update information in a separate file and join it the table to be updated — Strawberry, Jul 09 '16 at 16:49

score 1 · Answer 1 · answered Jul 09 '16 at 16:51

1

You can try building a single update with a CASE statement, like so:

UPDATE table1
    SET column1 = CASE
        WHEN cart_id = 1 THEN 11
        WHEN cart_id = 2 THEN 12
        ...
        ELSE cart_id = cart_id
    END

answered Jul 09 '16 at 16:51

Farlan

1,860
2
28
37

No. I have thousands rows, i do not want to write WHEN so many. – Piter Jul 09 '16 at 16:57
@Piter you don't write. Your scripts will build the request using string and arrays using a loop – Thanh Trung Feb 24 '18 at 21:40

score -1 · Answer 2 · answered Sep 18 '20 at 06:50

-1

INSERT INTO students 
(id, score1, score2)
VALUES 
    (1, 5, 8),
    (2, 10, 8),
    (3, 8, 3),
    (4, 10, 7)
ON DUPLICATE KEY UPDATE 
    score1 = VALUES(score1),
score2 = VALUES(score2);

answered Sep 18 '20 at 06:50

RDeCosterSF

16
2

How to update multiple rows in MySQL by one line?

2 Answers2