CODEWITHSUNDEEP
mysqldatabase
Real Noob
Real Noob

Reputation: 1593

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

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.

Upvotes: 0

Views: 64

Answers (2)

patrick3853
patrick3853

Reputation: 1130

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

Upvotes: 1

Roger Kreft
Roger Kreft

Reputation: 200

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;

Upvotes: 0

Related Questions