使用重复键将合并语句转换为MYSQL [英] Conversion of merge statement to MYSQL using on duplicate key

问题描述

请建议如何在MYSQL中转换此teradata语句.我们知道mysql不支持merge语句.选择查询中还使用了下面的2个表，每个表中都有多个主键.

Please suggest how to convert this teradata statement in MYSQL. As we know mysql doesn't support merge statement. Below 2 tables are also being used in select query and we have multiple primary key in each table.

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
    );

我正在尝试查询以下内容，但无法正常工作.

I am trying below query but it is not working.

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

ABC的主键= SUBJECT_ID

DEF的主键= SUBJECT_ID，WORKFLOW_ID，WORKFLOW_RUN_ID，WORKLET_RUN_ID，INSTANCE_ID，TASK_ID，START_TIME

Primary key of DEF= SUBJECT_ID,WORKFLOW_ID,WORKFLOW_RUN_ID,WORKLET_RUN_ID,INSTANCE_ID,TASK_ID,START_TIME

推荐答案

MySQL中正确的语法是:

The correct syntax in MySQL is:

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语法.使用它.

Note the use of proper, explicit, standard, readable JOIN syntax. Use it.

主要更改是

• 修复过时的语法.
• insert . . . select中的select不需要删除括号(尽管可能允许).
• 删除表别名，这是绝对不允许的.
• 修复on duplicate key语句.

• Fixing the archaic syntax.
• Removing the parentheses are not needed for the select in an insert . . . select (although they are probably allowed).
• Removing the table alias, which is definitely not allowed.
• Fixing the on duplicate key statement.

这篇关于使用重复键将合并语句转换为MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！