Jtdylktuy

In MySql is possible to make UPDATE using WHERE in for multiple users like:

 $users = ['user1', 'user2', 'user3'];

 $amount = 25;

 $users = implode(", ",array_keys($users));

"UPDATE balances SET balance = (balance + $amount) WHERE obj_id IN ($users)"

The question is: Is possible to make a WHERE IN insert similar to the example from bellow without using foreach loops before query?

 "INSERT INTO balances ('balance', 'user_id') VALUES (($amount), user_id) WHERE user_id IN ($users)"

MySQL does not support this, but assuming you are using PHP (you did not specify this, but your code looks like php), the workaround is fairly straight forward.

Also, keep in mind, INSERT INTO creates new rows, so WHERE does not apply. It adds to the bottom, and WHERE and ORDER BY can be used when retrieving the rows to sort the data.

You loop each value of the array, iterate it, and execute a query per iteration.

Your code would be something like this:

<?php



    //create array

    $user_ids = array('User1', 'User2', 'User3');

    $amount = 25;



    // go through each value in the array and execute the query

    foreach( $user_ids as $userid )

    {

        mysqli_query "INSERT INTO `balances` ('balance', 'user_id') 

                      VALUES (($amount), user_id)"; 

    }

?>

If you want to limit it to just one query, you can do the following:

<?php



    //create array

    $user_ids = array('User1', 'User2', 'User3');

    $amount = 25;



    // go through each value in the array and prepare the insert



    $values = 'VALUES';



    foreach( $user_ids as $userid )

    {

        $values .= '(' . $amount . ',' . $userid .'),';

    }

    $values = substr($values,0,-1);



    mysqli_query "INSERT INTO `balances` ('balance', 'user_id') " . $values;

?>