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
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);