您能帮我将以下OracleMERGE语句转换UPSERT为可在PostgreSQL 9.3数据库中使用的有效语句吗？
MERGE INTO my_table a
USING (SELECT v_c1 key,
v_c2 AS pkey,
v_c3 AS wcount,
v_c4 AS dcount
FROM DUAL) b
ON ( a.key = b.key
AND a.pkey = b.pkey
WHEN MATCHED
THEN
UPDATE SET wcount = b.wcount,
dcount = b.dcount
WHEN NOT MATCHED
THEN
INSERT (key,
pkey,
wcount,
dcount)
VALUES(b.key,b.pkey,b.wcount,b.dcount);
我认为PostgreSQL 9.3中没有UPSERT语句，但是您可以执行以下操作：
with cte_dual as (
select
v_c1 as key,
v_c2 as pkey,
v_c3 as wcount,
v_c4 as dcount
), cte_update as (
update my_table as a set
wcount = b.wcount,
dcount = b.dcount
from cte_dual as b
where b.key = a.key and b.pkey = a.pkey
returning *
)
insert into my_table (key, pkey, wcount, dcount)
select d.key, d.pkey, d.wcount, d.dcount
from cte_dual as d
where not exists (select * from cte_update as u WHERE u.key = d.key and u.pkey = d.pkey)
您可以阅读几个类似的问题：
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句