需要将Oracle的“合并"转换为Oracle.查询到PostgreSQL [英] Need to convert Oracle "merge into" query to PostgreSQL
本文介绍了需要将Oracle的“合并"转换为Oracle.查询到PostgreSQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将以下Oracle查询转换为PostgreSQL:
I'm trying to convert the following Oracle query to PostgreSQL:
MERGE into feepay.TRPT_W2_REPORTS TRPT1
USING(
WITH RWS AS
(SELECT PROG.BINCLIENT, TRPT.PUT_DIRECTORY
FROM feepay.program2 PROG
INNER JOIN feepay.TRPT_W2_PROGRAMS TRPT
ON (PROG.BINCLIENT = TRPT.BINCLIENT OR PROG.ISSUER_ID = TRPT.ISSUER_ID))
SELECT TCI.CUSTOMERNAME AS ACCOUNT,
TC.CUSTOMER_ID AS urn,
TC.LAST_NAME,
TC.FIRST_NAME,
TC.DOB,
TCA.ADDRESS
FROM feepay.TAU_CARDNUMBERS TCN
INNER JOIN feepay.TAU_CUSTOMER_CARDNUMBER TCCN ON (TCN.CARDNUMBER_ID = TCCN.CARDNUMBER_ID)
INNER JOIN feepay.TBLCUSTOMERS TC ON (TCCN.CUSTOMER_ID = TC.CUSTOMER_ID)
LEFT JOIN feepay.tau_customeraddress TCA ON (TC.CUSTOMER_ID = TCA.CUSTOMER_ID)
INNER JOIN feepay.TAU_ISSUER TI ON (TI.ISSUER_ID = TCN.ISSUER_ID)
INNER JOIN feepay.TBLCUSTOMERS TCI ON (TCI.CUSTOMER_ID = TI.CUSTOMER_ID)
LEFT JOIN feepay.TRPT_W2_REPORTS TRPT ON (TRPT.URN = TC.CUSTOMER_ID)
WHERE BINCLIENT IN (SELECT BINCLIENT FROM RWS)
AND TC.CUSTOMERNAME NOT IN ('freepay card','svds card')) TRPT2
ON (TRPT1.URN = TRPT2.URN)
WHEN MATCHED THEN
UPDATE SET
TRPT1.ACCOUNT = TRPT2.ACCOUNT,
TRPT1.LAST_NAME = TRPT2.LAST_NAME,
TRPT1.FIRST_NAME = TRPT2.FIRST_NAME,
TRPT1.DOB = TRPT2.DOB,
TRPT1.ADDRESS = TRPT2.ADDRESS,
TRPT1.LAST_UPDATE = now(),
TRPT1.STATUS = 'u' /* uPDATED */
WHEN NOT MATCHED THEN
INSERT (ACCOUNT, URN, LAST_NAME, FIRST_NAME, ISENTITY, DOB, ADDRESS, LAST_UPDATE, STATUS)
VALUES (TRPT2.ACCOUNT, TRPT2.URN, TRPT2.LAST_NAME, TRPT2.FIRST_NAME, 'y', TRPT2.DOB, TRPT2.MIDDLE_NAME,
TRPT2.ADDRESS, now(), 'i');
不幸的是PostgreSQL不支持MERGE,所以我真的很坚持.我希望这里的一些数据库专业人士可以帮助我.
Unfortunately PostgreSQL does not support MERGE, so I'm really stuck. I hope some database pro out of here can help me with this.
推荐答案
您可以改用INSERT ON CONFLICT ()
:
insert into feepay.TRPT_W2_REPORTS (ACCOUNT, URN, LAST_NAME, FIRST_NAME, ISENTITY, DOB, ADDRESS, LAST_UPDATE, STATUS)
WITH RWS AS (
SELECT PROG.BINCLIENT, TRPT.PUT_DIRECTORY
FROM feepay.program2 PROG
INNER JOIN feepay.TRPT_W2_PROGRAMS TRPT
ON (PROG.BINCLIENT = TRPT.BINCLIENT OR PROG.ISSUER_ID = TRPT.ISSUER_ID)
)
SELECT TCI.CUSTOMERNAME,
TC.CUSTOMER_ID,
TC.LAST_NAME,
TC.FIRST_NAME,
'Y'
TC.DOB,
TCA.ADDRESS,
now(),
'i'
FROM feepay.TAU_CARDNUMBERS TCN
INNER JOIN feepay.TAU_CUSTOMER_CARDNUMBER TCCN ON (TCN.CARDNUMBER_ID = TCCN.CARDNUMBER_ID)
INNER JOIN feepay.TBLCUSTOMERS TC ON (TCCN.CUSTOMER_ID = TC.CUSTOMER_ID)
LEFT JOIN feepay.tau_customeraddress TCA ON (TC.CUSTOMER_ID = TCA.CUSTOMER_ID)
INNER JOIN feepay.TAU_ISSUER TI ON (TI.ISSUER_ID = TCN.ISSUER_ID)
INNER JOIN feepay.TBLCUSTOMERS TCI ON (TCI.CUSTOMER_ID = TI.CUSTOMER_ID)
LEFT JOIN feepay.TRPT_W2_REPORTS TRPT ON (TRPT.URN = TC.CUSTOMER_ID)
WHERE BINCLIENT IN (SELECT BINCLIENT FROM RWS)
AND TC.CUSTOMERNAME NOT IN ('freepay card','svds card')) TRPT2
ON CONFLICT (URN)
DO UPDATE SET
ACCOUNT = excluded.ACCOUNT,
LAST_NAME = excluded.LAST_NAME,
FIRST_NAME = excluded.FIRST_NAME,
DOB = excluded.DOB,
ADDRESS = excluded.ADDRESS,
LAST_UPDATE = now(),
STATUS = 'u' /* uPDATED */
您需要验证SELECT列表中的列是否与INSERT列列表中列出的列匹配.
You need to verify if the columns in the SELECT list match the columns as listed in the INSERT column list.
这篇关于需要将Oracle的“合并"转换为Oracle.查询到PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文