MySQL INSERT ON DUPLICATE UPDATE

I have a complicated issue with creating a single SQL statement to work. I have three tables, active, inactive and history. They are identical, except that only field pointid in active is marked as primary key.

Struct:
-pointid int
-income_time timestamp
-outcome_time timestamp
-receipted_time timestamp
-type int
-isack int

Im inserting query into active using "INSERT INTO active (pointid, income_time, outcome_time, receipted_time, type, isack) VALUES (100, CURRENT_TIMESTAMP, NULL, NULL, 15, 0) ON DUPLICATE KEY UPDATE.....

and need help to construct end of the query, that it will function as following:

-if row with pointid does not exist, do simple insert

-if it does exist, check if type is same. if it is, do nothing

-if type is diffe开发者_StackOverflownent, and ack = 0, copy row to inactive (and set outcome_time to CURRENT_TIMESTAMP), then update current row in active (type, income_time, isack).

-if type if different, and ack = 1, copy row to history (and set outcome_time to CURRENT_TIMESTAMP), then update current row in active (type, income_time, isack).

logically, I think this might be possible using IF statements, but hasnt yet found a working solution. Any ideas?

I think this is beyond the ability of a single query. It can't conditionally update a different table altogether. You can use a trigger, write a stored procedure, or just have a sane schema that doesn't require this.