Mysql – using triggers to synchronize data

MySQLtrigger

i've two database, Database1 and Database2! i'd like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I've used triggers to synchronise data and it works but it duplicates everything say number, name, amount yet i want it to update, say if name jovia with a number 014567 and her previous amount(balance) is 200 in Database2. And if payment is made in Database1, with the same name and number and with 300 as the amount, i'd like the trigger to synchronize the other table by adding up the previous amount and the new amount, it does that but it duplicate the rows.

I've added a LIMIT 1 to avoid duplicate rows but it's the same issue

Database2 cc_phonenumber_table

-------------------------
name  | number | amount
-------------------------
jovia | 014567 | 200
maria | 098765 | 500
jovia | 014567 | 500
muche | 987653 | 245

how can avoid this duplication in the cc_phonenumber_table? please help me below is my trigger i've tried to use;

delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
  FOR EACH ROW
  BEGIN
    UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
    INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount);
       END;
    |


delimiter ;

Best Answer

Well thanks everyone, but I read somewhere saying that i should include this in the table i want to sync to(to make the table unique) "alter table "YOURTABLE add unique index(row1, row2);"

then i add ON DUPLICATE KEY UPDATE name = name and number = number;

Here is a final trigger after making the table unique to the one am synchronize to, in this case it's "database2.cc_phonenumber" and everything worked perfectly. 

    delimiter |

CREATE TRIGGER database1 BEFORE INSERT ON pesapi_payment
  FOR EACH ROW
  BEGIN
    UPDATE database2.cc_phonenumber SET amount=amount+new.amount where id=new.amount
    INSERT INTO database2.cc_phonenumber(number, name, amount) values (new.phonenumber, new.name, new.amount) ON DUPLICATE KEY UPDATE name = name and number = number;
       END;
    |


delimiter ;
Related Question