How to update 2 rows in mysql with one update?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

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