I can use this code to realize update for " update set amount = amount -1 where id = 1"

tx.Table(dao.TInvoiceExpense).Where("id = ?",1).Updates(map[string]interface{}{ "amount": gorm.Expr("amount - ?", 1)})

how to realize batch update for "update set amount = amount -1 where id = 1; update set amount = amount -2 where id = 2; " call mysql once.

Comment From: ivila

you can achieve it by using the case when syntax in mysql, but what I suggest is doing it in transaction rather than doing it in one query, I think in many senarios, the time of calling mysql doesn't matter so much.

Comment From: Sidneyxt

you can achieve it by using the case when syntax in mysql, but what I suggest is doing it in transaction rather than doing it in one query, I think in many senarios, the time of calling mysql doesn't matter so much.

It is in transaction, the time of calling mysql is matter to my case. This is just a demo, i need to handle million data, so i need calling mysql once to handle for such as 500 data.

Comment From: ivila

May I know what you are updating. 1. If you are doing some historical operation data insertion, you may just use the "on conflict update" syntax with batch insertion. 2. If you are doing update, like syncing order status of a tons of your records, and those records just need eventual consistency, you can try to split those records into smaller part and dispatch them into MQ, to update them asynchronously. 3. If method 2 is not suitable(for some scenarios, records must be strongly consistency, it's rare, but it happens), you can just use a "case when syntax" building query yourself, but keep in mind, a transaction of million data updating is heavily load to MySQL server, checking with your DBA in the company for suggestion first so you won't fuck up the DB.

Comment From: ivila

also, keep the order of the records to prevent potential dead locks.

Comment From: Sidneyxt

May I know what you are updating.

  1. If you are doing some historical operation data insertion, you may just use the "on conflict update" syntax with batch insertion.
  2. If you are doing update, like syncing order status of a tons of your records, and those records just need eventual consistency, you can try to split those records into smaller part and dispatch them into MQ, to update them asynchronously.
  3. If method 2 is not suitable(for some scenarios, records must be strongly consistency, it's rare, but it happens), you can just use a "case when syntax" building query yourself, but keep in mind, a transaction of million data updating is heavily load to MySQL server, checking with your DBA in the company for suggestion first so you won't fuck up the DB.

I tried use "on conflict update" syntax with batch insertion, but this only support set a value to update like "amount = 1", but i can't use it to update like "amount = amount +1" this is the point.

MQ performance improvement is not so much and make this update complex.

I am realize a invoice system, user apply sometimes need to handle many invoice details.I will update apply amount and invoice details in transaction.