Update columns values using select from external database and table in MariaDB trigger

Question

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using triggers for achieve this. Now at pdone db I have a table targets and at veevan I have a table address_vod__c. Every time a new record is inserted on address_vod__c or every time a record is updated I need to update the row at pdone.targets so this is how the process should run:

• INSERT a new record on veevan.address_vod__c, then SELECT the current inserted row (the values) find the match on pdone.targets and UPDATE columns
• UPDATE a record on veevan.address_vod__c, then SELECT the current updated row (the values) find the match on pdone.targets and UPDATE columns

This is what I have so far:

USE `veevan`;

DELIMITER $$

DROP TRIGGER IF EXISTS veevan.account_ai$$
USE `veevan`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1); 

        UPDATE pdone.targets
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        FROM (
            SELECT 
            address_vod__c.name AS `address1`,
            address_vod__c.address_line_2_vod__c AS `address2`,
            address_vod__c.city_vod__c AS `city`,
            address_vod__c.state_vod__c AS `state`,
            address_vod__c.zip_vod__c AS `zip`,
            address_vod__c.phone_vod__c AS `phone`,
            address_vod__c.fax_vod__c AS `fax`
            FROM
                address_vod__c avc
            WHERE
                address_vod__c.account_vod__c = NEW.account_vod__c
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                    AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod')
        )
        WHERE 
            targets.veeva_account_id=NEW.account_vod__c;
    END IF;
END$$
DELIMITER ;

But I don't know if the UPDATE using that SELECT will works and that is my doubt, how do I achieve an UPDATE from SELECT? Any time I try to run that SQL on I got this error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM SELECT address_vod__c.name AS address1, addr' at line 19

Can any give me some feedback and|or help on this?

veeva_account_id is the PK on pdone.targets and account_vod__c is the FK (kind of since that DB has not real relationships) on veevan.address_vod__c.

Answer 1

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw:

Errors:

• You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$.
• Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger.

Modifications:

• I edited the DROP TRIGGER with veevan.address_vod__c_ai instead veevan.account_ai.
• I used a JOIN with the same fields of your SELECT, with (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c) in the UPDATE.

TRIGGER:

USE `veevan`;
DELIMITER $$
DROP TRIGGER IF EXISTS veevan.address_vod__c_ai$$
USE `veeva_new`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1); 

        UPDATE pdone.targets
        JOIN veevan.address_vod__c AS avc ON (address_vod__c.account_vod__c = NEW.account_vod__c AND targets.veeva_account_id=NEW.account_vod__c)
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        WHERE address_vod__c.account_vod__c = NEW.account_vod__c
            AND external_id_vod__c IS NOT NULL
            AND address_vod__c.primary_vod__c = 1
            AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod');

    END IF;
END$$
DELIMITER ;

Try it!