title: Ways to update a dependent table in the same MySQL transaction?
body: <p>I need to update two tables inside a single transaction. The individual queries look something like this:</p>
  
  <pre><code>1. INSERT INTO t1 (col1, col2) 
     VALUES (val1, val2) 
     ON DUPLICATE KEY 
        UPDATE col2 = val2;
  </code></pre>
  
  <p>If the above query causes an insert then I need to run the following statement on the second table:</p>
  
  <pre><code>2. INSERT INTO t2 (col1, col2) 
     VALUES (val1, val2) 
     ON DUPLICATE KEY  
        UPDATE col2 = col2 + val2;
  </code></pre>
  
  <p>otherwise,</p>
  
  <pre><code>3. UPDATE t2 
         SET col2 = col2 - old_val2 + val2 
       WHERE col1 = val1; 
     -- old_val2 is the value of 
        t1.col2 before it was updated
  </code></pre>
  
  <p>Right now I run a SELECT on t1 first, to determine whether statement 1 will cause an insert or update on t1. Then I run statement 1 and either of 2 and 3 inside a transaction. What are the ways in which I can do all of these inside one transaction itself?</p>
  
  <p>The approach I was thinking of is the following:</p>
  
  <pre><code>UPDATE t2, t1 
     set t2.col2 = t2.col2 - t1.col2 
   WHERE t1.col1 = t2.col2 
     and t1.col1 = val1;
  
  INSERT INTO t1 (col1, col2) 
  VALUES (val1, val2) 
  ON DUPLICATE KEY 
     UPDATE col2 = val2;
  
  INSERT INTO t2, t1 (t2.col1, t2.col2) 
  VALUES (t1.col1, t1.col2) 
  ON DUPLICATE KEY 
     UPDATE t2.col2 = t2.col2 + t1.col2 
  WHERE t1.col1 = t2.col2 
    and t1.col1 = val1;
  </code></pre>
  
  <p>Unfortunately, there's no multi-table INSERT... ON DUPLICATE KEY UPDATE in MySQL 5.0. What else could I do?</p>