Ways to update a dependent table in the same MySQL transaction?

I need to update two tables inside a single transaction. The individual queries look something like this:

1. INSERT INTO t1 (col1, col2) 
   VALUES (val1, val2) 
   ON DUPLICATE KEY 
      UPDATE col2 = val2;

If the above query causes an insert then I need to run the following statement on the second table:

2. INSERT INTO t2 (col1, col2) 
   VALUES (val1, val2) 
   ON DUPLICATE KEY  
      UPDATE col2 = col2 + val2;

otherwise,

3. UPDATE t2 
       SET col2 = col2 - old_val2 + val2 
     WHERE col1 = val1; 
   -- old_val2 is the value of 
      t1.col2 before it was updated

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?

The approach I was thinking of is the following:

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 IN开发者_如何转开发TO 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;

Unfortunately, there's no multi-table INSERT... ON DUPLICATE KEY UPDATE in MySQL 5.0. What else could I do?

---

If you execute an INSERT or an UPDATE, your client is able to fetch the number of rows that changed. How to do this depends on your client, but for several programming languages, this number is returned by your INSERT if it was successful.

If you execute INSERT...ON DUPLICATE KEY UPDATE, you can as well fetch this number, but it is not exactly what you expect it to be. If you insert/update a single row, you receive 1 as the number of rows that changed in case of an INSERT and 2 in case of an UPDATE, even though there is only one row that changed. You could use this number to decide on the client side which query to run next.

Not as nice as a single transaction, but at least you get rid of one SELECT.

---

Okay, so I've got this figured out and done in a way that I like it:

 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 (col1, col2) VALUES (val1, val2) 
ON DUPLICATE KEY UPDATE
   col2 = col2 + VALUES(col2);

The third query can be rewritten to refer to values from t1 like such:

INSERT INTO t2 (col1, col2)
   SELECT col1, col2 FROM t1 WHERE col1 = val1
ON DUPLICATE KEY UPDATE
   t2.col2 = t2.col2 + VALUES(col2);