How to update 2 rows in mysql with one update?

Question

How can I update with one query? I want to do something like this:

update customer 
set balance = (400,150)  where customer_id IN ('2','3');

customer 2 will get a new balance of 400 and customer 3 will get 150.

I want 1 query because I'm using spring-boot, JPA

@Modifying
@Query("update customer set balance = (400,150)  where customer_id IN ('2','3');")

Can I do here 2 queries? for each customer? what is recommended? what is acceptable?

thanks.

Now if only Hibernate could do that for you... Enable batch processing for both Hibernate and MySQL, write your code as usual and Hibernate will rewrite the statement to a single batch statement. Nothing you, apart for the configuration, have to do for that. — M. Deinum, Aug 16 '22 at 13:32

score 2 · Answer 1 · answered Aug 16 '22 at 13:14

2

You can do by this way -

Update customer
   SET balance = (case when customer_id = '2' then '400'
                         when customer_id = '3' then '150'
                    end)
  WHERE
   customer_id IN ('2','3');

answered Aug 16 '22 at 13:14

Manik

264
2
8

nstvnsn · Answer 2 · 2022-08-16T13:23:50.827

The CASE statement may be what you are looking for.


UPDATE customer
    SET balance = (case
                    when customer_id = 1 then 150
                    when customer_id = 2 then 300
                    end)        
    WHERE ID in (1,2);

If your customer_id is of type string, add quotes to the customer_id numbers.

My example is just a modified version of:


Example Code:

UPDATE students
    SET JavaScore = (case
                    when ID = 1 then 75
                    when ID = 2 then 80
                    when ID = 3 then 86
                    when ID = 4 then 55
                    end),
        PythonScore = (case
                    when ID = 1 then 70
                    when ID = 2 then 85
                    when ID = 3 then 94
                    when ID = 4 then 75
                    end)
    WHERE ID in (1,2,3,4);

From this website:

DelftStack

v.ladynev · Answer 3 · 2022-08-16T13:46:17.370

if you want to use the spring-data way, you have to use complex SQL/JPQL as less as possible.

@Entity
class CustomerEntity {

}

@Modifying
@Query("update CustomerEntity customer set customer.balance = :balance where customer.id = :customerId")
int updateCustomerBalance(@Param("customerId") String customerId, @Param("balance") String balance);

customerRepository.updateCustomerBalance("2", "400");
customerRepository.updateCustomerBalance("3", "150");

Common transaction

if you want to update happens in one transaction

@Transactional
void doUpdate() {
    customerRepository.updateCustomerBalance("2", "400");
    customerRepository.updateCustomerBalance("3", "150");
}

Keep in mind that you have to call service.doUpdate() from outside. if you call the method from another service method, transaction will not be created.

Check that update has happened

int count = customerRepository.updateCustomerBalance("2", "400");
if (count == 0) {
    log.error("Customer not updated customerId=2 customerBalance=400");
}

score 0 · Answer 4 · answered Aug 16 '22 at 13:41

Hibernate can do this for you, no need to write your own query.

The steps.

Set hibernate.jdbc.batch_size to some reasonable size.
Enable insert/update query ordering
Enable statement rewrites for MySQL (set rewriteBatchedStatements to true)

In your application.properties add the following

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
# spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true # Needed when using versioned data
spring.datasource.hikari.dataSourcePoperties.rewriteBatchedStatements=true

See also this and this for a bit more background.

Now in your code you can just update and save your customers and the database will receive only 1 query.

How to update 2 rows in mysql with one update?

4 Answers4