title: update (or insert) multiple rows in one query
body: <p>I am trying to update multiple rows in a table at once (or insert them if they do not exist) given settings and an array of user id's.</p>
  
  <p>An example of how I select all rows from the <code>settings</code> table for all users of specific computers of a specific account.  This is the result set which I need to insert values in or update if they exist.</p>
  
  <pre><code>$stmt = $db-&gt;prepare("      
      SELECT settings.*
      FROM
          ( SELECT  account_id, computer_id
              FROM computers
              ORDER BY computer_id ASC LIMIT 0, ".$_SESSION['user']['licenses']."
          ) as c
          LEFT JOIN users
              on users.computer_id = c.computer_id
          LEFT JOIN accounts
              on accounts.account_id = c.account_id
          LEFT JOIN settings
              on settings.user_id = users.user_id
      WHERE accounts.account_id = ".$_SESSION['user']['account_id']."
  ");
  </code></pre>
  
  <p><strong>What I am trying to do :</strong>
  I am trying to update/insert three columns (enabled, status, and user_id) in the <code>settings</code> table for only those user ids listed in the array.  Enabled and status values will be the same for all, but the user_id will be different for each. </p>
  
  <p>$users = array(12, 36, 43, 56, 76)</p>
  
  <p>$binding = array(
      'enabled' => 1,
      'status' => 2,
      'user_id' => <em>from the array</em>
      );</p>
  
  <p>If my thought process is correct I can create a virtual table from my statement listed above then use ON DUPLICATE KEY UPDATE to insert/update on the results of that virtual table?</p>
  
  <p>Is this possible?  If so, example, tips, or point in the right direction?  The way I have working involves a foreach loop for each user id in the given array so there will be x number of queries depending on its count.  If I can cut that to only one that would be great!</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Ok, I am totally confused now... this is an attempt for defined values which is not working... and I still need a way to do so for every user_id in my array as mentioned above.  Don't I need to 'save' my entire FROM clause AS a new reference as well?</p>
  
  <pre><code>$stmt = $db-&gt;prepare("
      INSERT INTO settings (user_id, enabled, status)
      VALUES (:user_id, :enabled, :alert_user)    
      SELECT user_id, enabled, status
      FROM
          ( SELECT  account_id, computer_id
              FROM computers
              ORDER BY computer_id ASC LIMIT 0, ".$_SESSION['user']['licenses']."
          ) as c
          LEFT JOIN users
              on users.computer_id = c.computer_id
          LEFT JOIN accounts
              on accounts.account_id = c.account_id
          LEFT JOIN settings
              on settings.user_id = users.user_id
          WHERE accounts.account_id = ".$_SESSION['user']['account_id']."
      ON DUPLICATE KEY UPDATE enabled = VALUES(enabled), status = VALUES(status)  
  ");
  
  $binding = array(
      'enabled' =&gt; 1,
      'alert_user' =&gt; 4,
      'user_id' =&gt; 6
      );
  
  $stmt-&gt;execute($binding);
  </code></pre>