Add method batchUpdate in \yii\db\QueryBuilder

[dimmitri]

Very often in practice I have to do a bulk update of rows.

IN PostgreSQL:

UPDATE table AS t
SET
    col1 = c.col1,
    col2 = c.col2
FROM (VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4) ) AS c (id, col1, col2)
WHERE c.id = t.id;

or

INSERT INTO table (id, col1, col2) 
      VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
ON CONFLICT (id) DO UPDATE SET 
  col1 = EXCLUDED.col1,
  col2 = EXCLUDED.col2

IN MySQL:

INSERT INTO table (id, col1, col2)
     VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2);

I'm sure other databases have similar capabilities. For each DBMS will have its own specific implementation of the method batchUpdate in descendant classes.

Sorry for my bad english.

[samdark]

Looks like a good idea. Further research on how to do it in MSSQL, Oracle and SQLite is needed to move forward to implementing it.

[bscheshirwork]

@dimmitri good idea. So... I see some indeterminate stages: how to get inserted ids? How to separate inserted and updated rows?

[dimmitri]

I think it is necessary to first look at ALL the options implemented in different DBMS. To identify common features. In PostgreSQL in at least two ways to do it, in MySQL I know of one, but maybe more of them. I've never used MSSQL, Oracle and SQLite.

How to separate inserted and updated rows?

PostgreSQL. In this case, for example, insert is not happening:

UPDATE table AS t
SET
    col1 = c.col1,
    col2 = c.col2
FROM (VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4) ) AS c (id, col1, col2)
WHERE c.id = t.id;

[Vovan-VE]

in MySQL I know of one, but maybe more of them

In MySQL UPDATE with JOIN can be used for example. Something like this:

UPDATE `table` AS `t` 
    JOIN (
        SELECT 1 AS `id`, 1 AS `col1`, 1 AS `col2`
        UNION SELECT 2, 2, 2
        UNION SELECT 3, 3, 3
    ) AS `c`
        ON `t`.`id` = `c`.`id`
SET
    `t`.`col1` = `c`.`col1`,
    `t`.`col2` = `c`.`col2`;

[bscheshirwork]

@Vovan-VE i.e. need n subquery + 1 query vs n query if you can directly update row-by-row?

[Tigrov]

Related with #67 perhaps double. Looks like need batchUpsert() for both issues.