title: Mysql Update select subquery vs trigger loop
body: <p><strong>Hello guys.</strong><br><br>
  I've an issue  with a simple query.<br>
  Here we go, that's the code.</p>
  
  <pre><code>UPDATE user_resources AS ures
                  LEFT JOIN user_buildings as ub
                  ON ub.city_id = ures.city_id
                  INNER JOIN building_consumption AS bcons
                  ON bcons.resource_id = ures.resource_id
                  SET ures.quantity = ures.quantity - abs(FORMULA HERE that requires 
           building level and consumption at lvl 1 [default])
                      WHERE
                  (SELECT COUNT(id) FROM building_consumption AS bc2
   WHERE bc2.building_id=ub.building_id)  =
                      (SELECT COUNT(bc3.id) FROM building_consumption AS bc3
                        LEFT JOIN  tmp_user_resources AS ures
                          ON ures.resource_id = bc3.resource_id
                      WHERE ures.city_id = ub.city_id
                            AND bc3.building_id=ub.building_id
                            AND bc3.quantity&gt;0
                            AND IFNULL(ures.quantity, 0) - abs(FORMULA AGAIN);
  </code></pre>
  
  <p>I'll try to explain a bit.</p>
  
  <p>As you can imagine, this is for a game.
  Users (players) can  has different buildings in different cities.</p>
  
  <p>tab user_buildings </p>
  
  <pre><code>|id, city_id, buildings_id, level, usage|
  </code></pre>
  
  <p>A building can produce different resources 
  tab building_production</p>
  
  <pre><code>|id, building_id, resource_id, quantity_h|
  </code></pre>
  
  <p>but it can consume some resources too:
  tab building_consumption</p>
  
  <pre><code>|id, building_id, resource_id, quantity_h|
  </code></pre>
  
  <p>Obviously a building cannot produce if there are not enough resources to consume for his job.
  That's why I'm trying to compare WHERE SELECT COUNT how many resources it has to consume AND how many resources it can actually consume.</p>
  
  <p>Mysql does NOT ALLOW to subquery same table inside an UPDATE stmt.</p>
  
  <p>Using a cursor + loop is too much slow. I prefer to use different solution.</p>
  
  <p>Temp table could be a solution but my problem now is how to update the temp table without triggers? (UPDATE + SELECT fires triggers and to avoid endless loops mysql block the query, and i can't pause/resume triggers because </p>
  
  <pre><code>  IF ((@TRIGGER_CHECKS = FALSE)
        OR (@TRIGGER_BEFORE_INSERT_CHECKS = FALSE))
      AND (USER() = 'root@localhost')
    THEN
      LEAVE thisTrigger;
    END IF;
  </code></pre>
  
  <p>is inside the trigger itself).
  <br><br>
  I am open to all your suggestions!<br>
  <strong>Thanks</strong></p>
  
  <p>P.S. The code must be inside a scheduled event.</p>