How to update a table in different database with data from current table?

Question

I have two table in two separate databases. However, they are supposed to have the same data. I would like to make sure that whenever I make changes to data in table_a from database_a, they get reflected in table_b from database_b.

Is there any MySQL command that I can run to achieve this?

I read this question: Copy Data from a table in one Database to another separate database but it seems to insert data instead of updating it.

Thanks.

Are both databases on the same mysql installation on a single server, or are they on separate servers? — patrick3853, Jul 24 '20 at 05:46

patrick3853 · Accepted Answer · 2020-07-24T06:31:52.340

The best way to accomplish this would be with triggers. I haven't tested this, but it gives you the idea.

DELIMITER $$

CREATE
    TRIGGER table_a_after_insert AFTER INSERT 
    ON database_a.table_a
    FOR EACH ROW BEGIN
        -- update to match columns in your table
        INSERT INTO database_b.table_b (
            id,
            name
        )
        VALUES (
            NEW.id,
            NEW.name
        );
    END$$


CREATE
    TRIGGER table_a_after_update AFTER UPDATE
    ON database_a.table_a
    FOR EACH ROW BEGIN
        DECLARE updated_rows INT;

        -- again update the column list to match your table
        UPDATE database_b.table_b
        SET
            name = NEW.name
        WHERE id = NEW.id;

        -- in case the row didn't already exist in table_b, insert it
        SET @updated_rows = ROW_COUNT();
        IF updated_rows = 0
        THEN
            INSERT INTO database_b.table_b (
                id,
                name
            )
            VALUES (
                NEW.id,
                NEW.name
            );
        END IF;
    END$$


CREATE
    TRIGGER table_a_after_delete AFTER DELETE
    ON database_a.table_a
    FOR EACH ROW BEGIN
        -- obviously update the column list to match the columns in your table
        DELETE FROM database_b.table_b
        WHERE id = OLD.id;
    END$$

You'll have to make sure the user has the right privileges to database_b.table_b

Roger Kreft · Answer 2 · 2020-07-24T06:01:42.360

0

You can use Database triggers (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html) for something like this.

CREATE TRIGGER triggername AFTER INSERT ON database_a.table_a
  FOR EACH ROW
  BEGIN
    INSERT INTO database_b.table_b (id, ...) VALUES (NEW.id, ...);
  END;

You will however have to create triggers for each event. When inserting into table_a you need to insert into table_b, when deleting from table_a you will have to delete from table_b and so on.

€dit: Update for instance could look like this:

CREATE TRIGGER triggername AFTER UPDATE ON database_a.table_a  FOR EACH ROW
  UPDATE TABLE database_b.table_b
  SET table_b.id = NEW.id,
      ...(SET each column here)...
  WHERE table_b.id = NEW.id;

edited Jul 24 '20 at 06:01

answered Jul 24 '20 at 05:47

Roger Kreft

200
10

Thanks Roger. Will this also update the information of existing rows instead of just inserting new ones? – Real Noob Jul 24 '20 at 05:53
I would recommend using AFTER INSERT instead of BEFORE INSERT in case the query fails for some reason. – patrick3853 Jul 24 '20 at 06:01
1

@RealNoob i updated my post to me more clear. You will have to write a different statement for updating. And do not forget deletion ;). – Roger Kreft Jul 24 '20 at 06:02

How to update a table in different database with data from current table?

2 Answers2