title: UPDATE n records for each user
body: <p>I have to update n oldest orders per each user. For now I use following code to update 1 oldest record per user, but can't figure out how to update n records...</p>
  
  <pre><code>UPDATE orders
                  INNER JOIN (
                      SELECT id, MIN(created) AS created
                      FROM orders 
                      WHERE status="queue" AND type="order"
                      GROUP BY user_id
                  ) m ON orders.id = m.id
              SET orders.status = "process",
                  orders.lock_id ="somehash"
  </code></pre>
  
  <p>I found anserw:</p>
  
  <pre><code>set @num := 0, @type := "";
  
  UPDATE orders INNER JOIN(
  
  SELECT id, user_id, created, row_number FROM (
     SELECT id, user_id, created,
        @num := if(@type = user_id, @num + 1, 1) AS row_number,
        @type := user_id AS dummy
    FROM orders
    WHERE status = "queue"
    ORDER BY user_id, created asc ) AS grouped_orders 
  WHERE grouped_orders.row_number &lt;= 2
  
  ) m ON orders.id = m.id SET orders.status = "process", orders.lock_id = "somehash";
  </code></pre>