0

I came across a method of fast bulk update in mySQL recently:

The slow way:

UPDATE `example` SET `name` = 'Mary', `age` = '20' WHERE id = 2;
UPDATE `example` SET `name` = 'Nancy', `age` = '30' WHERE id = 4;
UPDATE `example` SET `name` = 'Oliver', `age` = '40' WHERE id = 5;

The fast way:

UPDATE `example` SET 
`name` = ELT(FIELD(id,2,4,5),'Mary','Nancy','Oliver') ,
`age` = ELT(FIELD(id,2,4,5),'20','30','40') 
WHERE id IN (2,4,5);

According to my google searching, the fast way is 50 times faster at large scale than the slow way, so I am wondering whether Laravel support this type of bulk update without the need to execute raw sql generated by myself.

Also if anyone is familiar with mysql and can tell me is this really that much faster or not, and if there is any scenario this method is actually worse I will be very graceful.

Edit(as requested in comment):

My current Laravel code would work like this for update:

foreach ($modelList as $model) {
    $model->setConnection($connection);
    $model->save();
}

What I would like for bulk update is

DB::connection($connection)->table($table)->bulkUpdate($models);

As I know currently Laravel already supports bulk insert as follows:

foreach ($models as $model) {
    $attributes = $model->getAttributes();
    $params[] = $attributes;
}

DB::connection($connection)->table($table)->insert($params);

which will generate one insert statement with multiple records instead of multiple insert statements.

cr001
  • 655
  • 4
  • 16
  • Can you [add](https://stackoverflow.com/posts/68408695/edit) [the Laravel code you already have](https://stackoverflow.com/help/minimal-reproducible-example) to your question? – shaedrich Jul 16 '21 at 12:06
  • 1
    @shaedrich I have added what I want exactly in code format – cr001 Jul 16 '21 at 12:15
  • Why do you do `$model->getAttributes()`? If you've already set the modified values in model, you can just use `$model->save()`. – shaedrich Jul 16 '21 at 12:18
  • you can use the sql in this possible duplicate and convert it to your query builder, https://stackoverflow.com/questions/3432/multiple-updates-in-mysql – Mohsen Nazari Jul 16 '21 at 12:18
  • @shaedrich when I do model->save() in a loop I will have two different insert sqls generated while if I do table()->insert(params) I will have one insert sql with two lines after "value" generated which is much faster. – cr001 Jul 16 '21 at 12:20
  • @MohsenNazari That's not what the TO asked for. – shaedrich Jul 16 '21 at 12:22
  • @cr001 Why do you set the modified values on the model anyway if you don't intend to `save()` it instead of passing them directly to the update method? – shaedrich Jul 16 '21 at 12:23
  • @shaedrich The MySql server is on a remote ip address so I want to modify models locally and bulk insert/bulk update at once to reduce overhead. – cr001 Jul 16 '21 at 12:28
  • Assuming `id` is the `PRIMARY KEY`, I can't see it being more than 3x faster, not 50x. And the clarity of the code, to me, outweighs the slowness. – Rick James Jul 16 '21 at 16:51
  • @RickJames The source is a Japanese website but ( qiita.com/yokozawa/items/13f837a27afc813eac32 ) this website shows empirically that the overall time is reduced to around 50 times for a dataset of 200 thousand rows. This theoretically is also possible if network traffic issues are taken into consideration. – cr001 Jun 13 '22 at 12:25

3 Answers3

1

If there's no solution, you can still do it raw with prepared statements:

DB::update("
    UPDATE `example` SET 
        `name` = ELT(FIELD(id,2,4,5),?,?,?) ,
        `age` = ELT(FIELD(id,2,4,5),?,?,?) 
    WHERE id IN (?,?,?)
", [
    'Mary', 'Nancy', 'Oliver',
    '20', '30', '40',
    2, 4, 5
]);

Technically, this should also work. Haven't checked it yet:

$sql = DB::table('users')->update([
  ['name' => DB::raw("ELT(FIELD(id,2,4,5),'Mary','Nancy','Oliver')")],
  ['age' => DB::raw("ELT(FIELD(id,2,4,5),'20','30','40')")]
])
->whereIn('id', [ 2, 4, 5 ]);
shaedrich
  • 5,457
  • 3
  • 26
  • 42
  • Thank you for the answer, actually that's the worst I am preparing for. The drawback is code looks ugly but if the framework does not support this I am afraid I will have to. However I cannot believe such a popular framework does not support this kind of needs, especially considering bulk insert is already supported. – cr001 Jul 16 '21 at 12:38
  • 1
    Gimme a couple minutes, I'm checking out an alternative. – shaedrich Jul 16 '21 at 12:40
  • 1
    Thank you for the alternative way, looks pretty clever. I have upvoted and will try it out later, if it work I will accept the answer. – cr001 Jul 16 '21 at 12:48
  • According to [this article](https://tomgrohl.medium.com/using-raw-expressions-for-updates-in-laravel-query-builder-96fc91e29b77) you can use raw expressions in `update()` but you still have to get the values in there. That'll be the problem. – shaedrich Jul 16 '21 at 12:49
  • 1
    As long as it works functionally I think generating the raw part would not be a big issue. The important part is code readability and I think generating only that raw part would be pretty structural and not make the code too messy. I will test it out tomorrow when I am back to work. – cr001 Jul 16 '21 at 12:53
  • You probably get `[Mary, 20, 2]` as a unit; this is easy to then provide to a single-row Update. But to get `[Mary, Nancy, Oliver]`, you need to gather a 2D array and transpose it before using the "batch" update. Is it worth the extra effort? – Rick James Jul 16 '21 at 16:57
  • If you put all the models in a collection, you can use `pluck()` on the respective attribute. – shaedrich Jul 16 '21 at 17:31
  • I have tried the solution but the error given is "General error: 1366 Incorrect integer value". This only happens if I run through Laravel, if I run the generated query in MySQL Workbench it works correctly. Still trying to figure out what is the reason – cr001 Jul 17 '21 at 07:05
  • It looks like affectingStatement function in Illuminate/Database/Connection.php fails on "$statement->execute();" despite the query generated being correct. I have also tried generating the whole raw sql myself and use DB::statement, this also works correctly. However the code is ugly. Seems really wierd. – cr001 Jul 17 '21 at 07:27
  • Would you mind, sharing the error message and your code around it? – shaedrich Jul 19 '21 at 10:18
  • The error message is as above: "General error: 1366 Incorrect integer value", I have asked for permission but the specific error message and code is not allowed to be shared. Laravel is likely to have used the "?" type parameterized sql instead of a complete sql, which means type check will fail for the "ELT" thing unless it is string. I have settled with the "write complete raw sql and execute DB::raw" way. – cr001 Jul 23 '21 at 07:31
0

Another approach:

INSERT INTO t
    (name, age, id)
VALUES
    ('Mary', 20, 2),
    ('Nancy', 30, 4),
    ('Oliver', 40, 5)
ON DUPLICATE KEY UPDATE name = VALUES(name);

Assuming the ids are already in the table and it has PRIMARY KEY(id), there would be any INSERTs, only UPDATEs -- which is what you want.

The name = VALUES(name) is a dummy no-op. It needs to change to name = NEW.name in MySQL 8.0.

From Comment...

If you must not INSERT "new" rows, then consider this:

INSERT INTO tmp
    (name, age, id)
    VALUES
    ('Mary', 20, 2),
    ('Nancy', 30, 4),
    ('Oliver', 40, 5);
UPDATE t
    JOIN tmp USING(name);

(I am not sure of the syntax; play around with the "multi-table update".)

This second 'solution' requires creating the tmp table and/or having it already available (plus possibly a TRUNCATE).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thank you for the answer. This approach has the drawback that it will not fail but will insert when the key is not in the table, which does not fit my specific project purpose. However I would be interested whether in this way one can use Laravel Query Builder and only write a minimum amount of raw Sqls. – cr001 Jul 17 '21 at 07:43
  • @cr001 - See if my addition solves your constraint. Sorry, I can't help with Query Builder. – Rick James Jun 19 '22 at 05:02
-3

UPDATE student set roll='12',fee=fee-10 where roll='15'

Use this example for your code. This is easier and fastest way to support any platform for mysql.

Susmita Dey
  • 1
  • 3