Multiple updates with one MySQL query

Working with a database with millions of rows poses a few challenges. One of them: Updating every row with unique data. Working with doctrine is basically impossible with such a big table as it's going to fill up your memory in no time.

One possible solution is to work with INSERT and ON DUPLICATE KEY UPDATE. With it you use an INSERT instead of UPDATE but add the primary key for the query to check for unique values.

This way you can add many thousand updates in one query.

INSERT INTO payment (id, provider, active)
VALUES
    (1, "first", 1),
    (2, "second", 1),
    (3, "third", 0),
    (4, "fourth", 1)
    ...
ON DUPLICATE KEY UPDATE
provider=VALUES(provider), active=VALUES(active);