trouble finding error in MYSQL 8 MERGE STATEMENT

Question

I have a CLOUDSQL (Mysql 8) on GCP. It has two databases: EDW and EDW_STAGE. Both have a LEADS table. The EDW_STAGE database is a staging database that will merge daily information into the EDW.

I'm trying to run a merge from EDW_STAGE to EDW. WHile every SQL checker states that I have good code, I get the below error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MERGE EDW.LEADS  AS MyTarget
USING EDW_staging.LEADS as MySource
     ON MyS' at line 1

My Code:

MERGE offrs.LEADS  AS MyTarget
USING offrs_staging.LEADS as MySource
     ON MySource.LEADID = MyTarget.LEADID
WHEN MATCHED THEN UPDATE SET 
    LEADSOURCE = MySource.LEADSOURCE,
    LEADTYPE = MySource.LEADTYPE,
    FNAME = MySource.FNAME,
    LNAME = MySource.LNAME,
    EMAIL = MySource.EMAIL,
    EMAIL2 = MySource.EMAIL2,
    PHONE1 = MySource.PHONE1,
    PHONE2 = MySource.PHONE2,
    ATTOM_ID = MySource.ATTOM_ID,
    GEOID = MySource.GEOID,
    SCORE = MySource.SCORE,
    AVM = MySource.AVM,
    ADDRESS1 = MySource.ADDRESS1,
    ADDRESS2 = MySource.ADDRESS2,
    CITY = MySource.CITY,
    ZIP = MySource.ZIP,
    LAT = MySource.LAT,
    LNG = MySource.LNG,
    STATUS = MySource.STATUS,
    LISTINGDATE = MySource.LISTINGDATE,
    DATESTAMP = MySource.DATESTAMP,
    LOAD_DATE = MySource.LOAD_DATE
WHEN NOT MATCHED THEN INSERT
    (
        LEADID, 
        LEADSOURCE, 
        LEADTYPE, 
        FNAME, 
        LNAME, 
        EMAIL, 
        EMAIL2, 
        PHONE1, 
        PHONE2, 
        ATTOM_ID, 
        GEOID, 
        SCORE, 
        AVM, 
        ADDRESS1, 
        ADDRESS2, 
        CITY, 
        ZIP, 
        LAT, 
        LNG, 
        STATUS, 
        LISTINGDATE, 
        DATESTAMP, 
        LOAD_DATE
    )
    VALUES (
        MySource.LEADID, 
        MySource.LEADSOURCE, 
        MySource.LEADTYPE, 
        MySource.FNAME, 
        MySource.LNAME, 
        MySource.EMAIL, 
        MySource.EMAIL2, 
        MySource.PHONE1, 
        MySource.PHONE2, 
        MySource.ATTOM_ID, 
        MySource.GEOID, 
        MySource.SCORE, 
        MySource.AVM, 
        MySource.ADDRESS1, 
        MySource.ADDRESS2, 
        MySource.CITY, 
        MySource.ZIP, 
        MySource.LAT, 
        MySource.LNG, 
        MySource.STATUS, 
        MySource.LISTINGDATE, 
        MySource.DATESTAMP, 
        MySource.LOAD_DATE
    );

Am I missing something here? or do I have to set a setting on MySQL to enable this? Any wisdom would be greatly appreciated.

Does this answer your question? [Is merge statement available in MySQL](https://stackoverflow.com/questions/42663074/is-merge-statement-available-in-mysql) — HoneyBadger, Jul 09 '22 at 14:44
Cf. https://bugs.mysql.com/bug.php?id=9018 for the open feature request in MySQL, filed in 2005! — Bill Karwin, Jul 09 '22 at 14:45

score 1 · Answer 1 · answered Jul 09 '22 at 14:56

As noted in the comments above, MySQL does not implement the SQL:2003 MERGE statement. All implementors of SQL choose which features of standard SQL they implement; there are no vendors who implement every feature of SQL.

Likewise, all SQL vendors have added their own proprietary extensions to SQL syntax. In the case of MySQL, the feature that is approximately like MERGE is INSERT...ON DUPLICATE KEY UPDATE..., although MERGE can do a lot more than MySQL's IODKU syntax.

Here's what it might look like in MySQL:

INSERT INTO MyTarget
    (
        LEADID,
        LEADSOURCE,
        LEADTYPE,
        FNAME,
        LNAME,
        EMAIL,
        EMAIL2,
        PHONE1,
        PHONE2,
        ATTOM_ID,
        GEOID,
        SCORE,
        AVM,
        ADDRESS1,
        ADDRESS2,
        CITY,
        ZIP,
        LAT,
        LNG,
        STATUS,
        LISTINGDATE,
        DATESTAMP,
        LOAD_DATE
    )
SELECT
    MySource.LEADID, 
    MySource.LEADSOURCE, 
    MySource.LEADTYPE, 
    MySource.FNAME, 
    MySource.LNAME,
    MySource.EMAIL,
    MySource.EMAIL2,
    MySource.PHONE1,
    MySource.PHONE2,
    MySource.ATTOM_ID,
    MySource.GEOID,
    MySource.SCORE,
    MySource.AVM,
    MySource.ADDRESS1,
    MySource.ADDRESS2,
    MySource.CITY,
    MySource.ZIP,
    MySource.LAT,
    MySource.LNG,
    MySource.STATUS,
    MySource.LISTINGDATE,
    MySource.DATESTAMP,
    MySource.LOAD_DATE
FROM MySource
ON DUPLICATE KEY UPDATE
    LEADSOURCE = VALUES(LEADSOURCE),
    LEADTYPE = VALUES(LEADTYPE),
    FNAME = VALUES(FNAME),
    LNAME = VALUES(LNAME),
    EMAIL = VALUES(EMAIL),
    EMAIL2 = VALUES(EMAIL2),
    PHONE1 = VALUES(PHONE1),
    PHONE2 = VALUES(PHONE2),
    ATTOM_ID = VALUES(ATTOM_ID),
    GEOID = VALUES(GEOID),
    SCORE = VALUES(SCORE),
    AVM = VALUES(AVM),
    ADDRESS1 = VALUES(ADDRESS1),
    ADDRESS2 = VALUES(ADDRESS2),
    CITY = VALUES(CITY),
    ZIP = VALUES(ZIP),
    LAT = VALUES(LAT),
    LNG = VALUES(LNG),
    STATUS = VALUES(STATUS),
    LISTINGDATE = VALUES(LISTINGDATE),
    DATESTAMP = VALUES(DATESTAMP),
    LOAD_DATE = VALUES(LOAD_DATE);

But the caveat is that this only works if your MyTarget.LEADID column is a primary or unique key. The ON DUPLICATE clause of this statement is activated only in a row that would otherwise fail to insert because it conflicts with a primary or unique key. If LEADID is not a primary or unique key, then the row isn't a duplicate, so it inserts a new row instead of updating. There is no feature in the IODKU statement for defining a custom column for the match.

trouble finding error in MYSQL 8 MERGE STATEMENT

1 Answers1

Linked

Related