duplicate键将merge语句转换为mysql

kcugc4gi 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(302)

请建议如何在mysql中转换这个teradata语句。众所周知，mysql不支持merge语句。下面两个表也用于select查询，每个表中有多个主键。

MERGE INTO XYZ USING (
            SELECT
                    ITRR.WORKFLOW_NAME WORKFLOW_NAME
            ,       ITRR.INSTANCE_NAME INSTANCE_NAME
            ,       MIN(ITRR.START_TIME) EARLIEST_START_TIME
            ,       ITRR.SUBJECT_AREA SUBJECT_AREA
            ,       'INFORMATICA' PLATFORM_NAME
            FROM
                    ABC IWRR
            ,       DEF ITRR
            WHERE
                    IWRR.WORKFLOW_RUN_ID = ITRR.WORKFLOW_RUN_ID
            AND     IWRR.USER_NAME IN ('xyz')
            AND     ITRR.RUN_STATUS_CODE <> 2
            GROUP BY
                    ITRR.WORKFLOW_NAME
            ,       ITRR.INSTANCE_NAME
            ,       ITRR.SUBJECT_AREA
    ) SRC
    ON
            XYZ.PARENT_JOB_NAME = SRC.WORKFLOW_NAME
    AND     XYZ.CHILD_JOB_NAME  = SRC.INSTANCE_NAME
    AND     XYZ.SANDBOX         = SRC.SUBJECT_AREA
    WHEN MATCHED THEN UPDATE SET FIRST_EXECUTION = SRC.EARLIEST_START_TIME
    WHEN NOT MATCHED THEN INSERT
    (
            PARENT_JOB_NAME
    ,       CHILD_JOB_NAME
    ,       FIRST_EXECUTION
    ,       SANDBOX
    ,       PLATFORM_NAME
    )VALUES
    (
            SRC.WORKFLOW_NAME
    ,       SRC.INSTANCE_NAME
    ,       SRC.EARLIEST_START_TIME
    ,       SRC.SUBJECT_AREA
    ,       SRC.PLATFORM_NAME
    );

我尝试下面的查询，但它不工作。

INSERT INTO XYZ (
                PARENT_JOB_NAME
,       CHILD_JOB_NAME
,       FIRST_EXECUTION
,       SANDBOX
,       PLATFORM_NAME
    )

       (SELECT
                ITRR.WORKFLOW_NAME WORKFLOW_NAME
        ,       ITRR.INSTANCE_NAME INSTANCE_NAME
        ,       MIN(ITRR.START_TIME) EARLIEST_START_TIME
        ,       ITRR.SUBJECT_AREA SUBJECT_AREA
        ,       'INFORMATICA' PLATFORM_NAME
        FROM
                ABC IWRR
        ,       DEF ITRR
        WHERE
                IWRR.WORKFLOW_RUN_ID = ITRR.WORKFLOW_RUN_ID
        AND     IWRR.USER_NAME IN ('XYZ')
        AND     ITRR.RUN_STATUS_CODE <> 2
        GROUP BY
                ITRR.WORKFLOW_NAME
        ,       ITRR.INSTANCE_NAME
        ,       ITRR.SUBJECT_AREA
 ) SRC
ON DUPLICATE KEY UPDATE
       FIRST_EXECUTION = SRC.EARLIEST_START_TIME

xyz的主键= PARENT_JOB_NAME 主键= SUBJECT_ID def的主键= SUBJECT_ID , WORKFLOW_ID , WORKFLOW_RUN_ID , WORKLET_RUN_ID , INSTANCE_ID , TASK_ID , START_TIME

sql mysql teradata merge teradata-sql-assistant

来源：https://stackoverflow.com/questions/61932378/conversion-of-merge-statement-to-mysql-using-on-duplicate-key

2条答案

按热度按时间

baubqpgj1#

mysql中正确的语法是：

INSERT INTO XYZ (PARENT_JOB_NAME, CHILD_JOB_NAME, FIRST_EXECUTION, SANDBOX, PLATFORM_NAME)
    SELECT ITRR.WORKFLOW_NAME, ITRR.INSTANCE_NAME,
           MIN(ITRR.START_TIME), ITRR.SUBJECT_AREA, 'INFORMATICA'
    FROM ABC IWRR JOIN
         DEF ITRR
         ON IWRR.WORKFLOW_RUN_ID = ITRR.WORKFLOW_RUN_ID 
    WHERE IWRR.USER_NAME IN ('XYZ') AND
          ITRR.RUN_STATUS_CODE <> 2
    GROUP BY ITRR.WORKFLOW_NAME, ITRR.INSTANCE_NAME, ITRR.SUBJECT_AREA
ON DUPLICATE KEY UPDATE FIRST_EXECUTION = VALUES(FIRST_EXECUTION);

注意使用适当的、明确的、标准的、可读的 JOIN 语法。使用它。
主要的变化是
修正了古老的语法。
不需要删除括号 select 在一个 insert . . . select （尽管他们可能是被允许的）。
删除表别名，这是绝对不允许的。
修复 on duplicate key 声明。