时间戳

首页 > 解决方案 > 如何将合并查询转换为插入覆盖查询

hive - 如何将合并查询转换为插入覆盖查询

问题描述

SQL -

MERGE into (target table) trg USING (source table) src ON src.incidentnumber = trg.incidentnumber

WHEN MATCHED AND (trg.status != src.status AND src.archernumber IS NOT NULL) THEN UPDATE SET status =src.status, archernumber=src.archernumber, load_timestamp=src.load_timestamp

WHEN NOT MATCHED THEN INSERT VALUES ( src.archernumber, src.incidentnumber, src.sent, src.recipient, src.cc, src.bcc, src.policyname, src.policyrule, src.dismisalreason, src.status, src.country, src.load_timestamp, src.load_job, src.sent_yyyymm)

标签： hivehql

解决方案

    UPDATE  trg
SET     trg.status = src.status,
        trg.archernumber = src.archernumber,
        trg.load_timestamp = src.load_timestamp
FROM    target table AS trg
INNER   JOIN source table AS src
ON      src.incidentnumber = trg.incidentnumber
WHERE   trg.status <> src.status AND src.archernumber IS NOT NULL


INSERT  INTO (target table)
(
    archernumber,
    incidentnumber,
    sent,
    recipient,
    cc,
    bcc,
    policyname,
    policyrule,
    dismisalreason,
    status,
    country,
    load_timestamp,
    load_job,
    sent_yyyymm
)
SELECT  src.archernumber,
        src.incidentnumber,
        src.sent,
        src.recipient,
        src.cc,
        src.bcc,
        src.policyname,
        src.policyrule,
        src.dismisalreason,
        src.status,
        src.country,
        src.load_timestamp,
        src.load_job,
        src.sent_yyyymm
FROM    source table AS src
LEFT    JOIN target table AS trg
ON      src.incidentnumber = trg.incidentnumber
WHERE   trg.incidentnumber IS NULL

推荐阅读