MySQL – Update Records Between Tables in Different Databases

MySQLperformancequery-performance

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for table db1.reps – the one who needs update through sync:

CREATE TABLE `db1.reps` (
  `veeva_rep_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `territories_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `display_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `avatar_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'default_avatar.png',
  `rep_type` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'VEEVA',
  `username` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `first` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `last` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bio` longtext COLLATE utf8_unicode_ci,
  `phone` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `inactive` tinyint(1) NOT NULL DEFAULT '0',
  `lastLoginAt` datetime DEFAULT NULL,
  `lastSyncAt` datetime NOT NULL,
  `repTokenId` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`veeva_rep_id`),
  KEY `IDX_485DE7B033B9A304` (`territories_id`),
  CONSTRAINT `FK_485DE7B033B9A304` FOREIGN KEY (`territories_id`) REFERENCES `territories` (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And this is the DDL for db2.user – kind of master:

CREATE TABLE `user` (
  `id` varchar(18) NOT NULL,
  `username` varchar(80) DEFAULT NULL,
  `lastname` varchar(80) DEFAULT NULL,
  `firstname` varchar(40) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  `lastmodifieddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I could do this by using programming but I want to know and learn, if this is possible, to do it through SQL. I need, foreach row, on db2.user perform an UPDATE on db1.reps but just when db2.user.lastmodifieddate > db1.reps.lastSyncAt, how? I must clear that db1.reps.lastSyncAt should be updated to NOW() if a UPDATE was runned on it. This is what I did so far:

    INSERT INTO db1.reps (
        display_name,
        username,
        `first`,
        `last`,
        email,
        lastSyncAt
    )(
        SELECT
            CONCAT(
                LCAPITAL (firstname),
                ' ',
                LCAPITAL (lastname)
            ) AS display_name,
            username,
            LCAPITAL (firstname),
            LCAPITAL (lastname),
            email,
            NOW()
        WHERE
            db1.reps.veeva_rep_id = db2.`user`.id
    )
    WHERE
        db2.`user`.lastmodifieddate > db1.reps.lastSyncAt ON DUPLICATE KEY UPDATE db1.reps.display_name =
    VALUES
        (
            CONCAT(
                LCAPITAL (db2.`user`.firstname),
                ' ',
                LCAPITAL (db2.`user`.lastname)
            )
        ),
        db1.reps.username =
    VALUES
        (db2.`user`.username),
        db1.reps.`first` =
    VALUES
        (LCAPITAL(db2.`user`.firstname)),
    db1.reps.`last` =
    VALUES
        (LCAPITAL(db2.`user`.lastname)),
    db1.reps.email =
    VALUES
        (db2.`user`.email)
    db1.reps.lastSyncAt =
    VALUES
        (NOW())

Can any give me some help?

EDIT

Because db2.user has duplicates (a table with no restrictions at all – see image below) I need to clean up a bit the trigger before execute.

enter image description here

This is how I am doing at SQL side:

SELECT
    count(*) AS reps,
    userid,
    lastmodifieddate,
    territoryid
FROM
    (
        SELECT
            userid,
            territoryid,
            count(*) AS territories
        FROM
            userterritory
        GROUP BY
            userid
        HAVING
            territories = 1
    ) T1
INNER JOIN (
    SELECT
        id AS userid,
        lastmodifieddate
    FROM
        `user`
    WHERE
        `user`.`id` IN (
            SELECT
                userterritory.`userid`
            FROM
                userterritory
        )
) T2 USING (userid)
GROUP BY
    territoryid
HAVING
    reps = 1

The query above guarantee to me just on reps to one territory as should be. How I should modify the trigger to accomplish this?

NOTE: LCAPITAL() is a function I have for normalize strings

Best Answer

You can use a TRIGGER to update db1.reps.lastSyncAt when db2.user.lastmodifieddate is updated.

Trigger:

DELIMITER $$

DROP TRIGGER IF EXISTS db2.user_BEFORE_UPDATE$$
USE `db2`$$
CREATE DEFINER=`root`@`%` TRIGGER `db2`.`user_BEFORE_UPDATE` BEFORE UPDATE ON `user` FOR EACH ROW
BEGIN


    SET @UserVerification=(select reps.veeva_rep_id from db1.reps where reps.veeva_rep_id=OLD.id);
    IF (@UserVerification>0) then
        SET @UserLastMod=(SELECT reps.lastSyncAt FROM db1.reps WHERE reps.veeva_rep_id=OLD.id LIMIT 0,1); 
        IF (NEW.lastmodifieddate > @UserLastMod) THEN
            # [Will update lastSyncAT to NEW.lastSyncAt]
            UPDATE db1.reps 
            SET reps.lastSyncAt = NEW.lastmodifieddate,
            reps.updatedAt=now() WHERE reps.veeva_rep_id=OLD.id; 
        END IF;
    ELSE
        INSERT INTO db1.reps
        (veeva_rep_id,territories_id,display_name,avatar_url,rep_type,username,first,last,title,bio,phone,email,inactive,lastLoginAt,lastSyncAt,repTokenId,createdAt,updatedAt)
        select
            u.id,
            null as territories_id,
            test.LCAPITAL(CONCAT(u.lastname,' ',u.firstname)) as display_name,
            'default avatar url' as avatar_url,
            'VEEVA' as rep_type,
            u.username,
            u.firstname as first,
            u.lastname as last,
            null as title,
            null as bio,
            null as phone,
            u.email,
            '' as inactive,
            null as lastLoginAt,
            u.lastmodifieddate as lastSyncAt,
            null as repTokenId,
            now() as createdAt,
            now() as updatedAt
        from db2.user as u
        where u.id = OLD.id;
    END IF;
END$$
DELIMITER ;

And if you want to INSERT into reps table when any row is add in user table:

DROP TRIGGER IF EXISTS db2.user_ai$$
USE `db2`$$

CREATE DEFINER = CURRENT_USER TRIGGER `db2`.`user_ai` AFTER INSERT ON `user` FOR EACH ROW
BEGIN
INSERT INTO pdone.reps
(veeva_rep_id,territories_id,display_name,avatar_url,rep_type,username,first,last,title,bio,phone,email,inactive,lastLoginAt,lastSyncAt,repTokenId,createdAt,updatedAt)
select
    NEW.id,
    null as territories_id,
    test.LCAPITAL(CONCAT(NEW.lastname,' ',NEW.firstname)) as display_name,
    'default avatar url' as avatar_url,
    'VEEVA' as rep_type,
    NEW.username,
    NEW.firstname as first,
    NEW.lastname as last,
    null as title,
    null as bio,
    null as phone,
    NEW.email,
    '' as inactive,
    null as lastLoginAt,
    NEW.lastmodifieddate as lastSyncAt,
    null as repTokenId,
    now() as createdAt,
    now() as updatedAt;
END$$
DELIMITER ;

By the way, you have to change your tables datatypes because 75-80% are just VARCHARs. The function LCAPITAL that I made can be use with more than 1 word, example:

SELECT test.LCAPITAL(CONCAT('oanRE',' ','AnTIGUA'));

Result: 

Oanre Antigua
Related Question