Ways to update a dependent table in the same MySQL transaction?
- by codie
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 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;
Unfortunately, there's no multi-table INSERT... ON DUPLICATE KEY UPDATE in MySQL 5.0. What else could I do?
