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!
The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent.
MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5.
With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some features were implemented in one but not other, some were implemented in both but with some differences (GTID, virtual/computed columns, differences in optimizer, ...).
So the answer is: There should be no difference in "standart" queries between using InnoDB/XtraDB or even MyISAM as both are "under" the SQL level of MySQL/MariaDB structure. You only have to be aware of differences in syntax for new features in 5.6+/10.0+ and those are not because of the engine differences (virtual columns for example are not handled by InnoDB afaik, but by the SQL layer - permanent computed column is just another normal column for InnoDB with the higher layer supplementing the data and the non-stored variant does not "touch" the engine at all and is always computed after the engine returns the columns needed).
Best Answer
MariaDB 10.3+
Yes, speaking of MariaDB 10.3.3 and later it seems to support table value constructors.
You'll notice that there is no support for column_alias in the
FROM
clause, instead requiring you to use the table_alias and the name of the first row's value for that column. But column_alias are allowed in a CTE'sWITH
clause,Additionally you may able to skirt this by giving the first row a definitive alias.
also see examples in https://jira.mariadb.org/browse/MDEV-12172
PostgreSQL permits column aliases in FROM clauses, as show above with
t(x)
, this syntax is also not supported in MariaDBMariaDB <10.3; MySQL 5.x & 8.x (Workaround)
Prior to MariaDB 10.3, and all versions of MySQL do not support the
VALUES
expression.However, it does support sub-selects with literals.
PostgreSQL also supports this syntax.
However, things get a lot more verbose with this syntax if you're doing multiple rows. Below from MariaDB, also works in PostgreSQL
Whereas with
VALUES
, it's simplyVALUES (1),(2)