如果不存在则插入，否则在Netezza中对其进行更新 [英] Insert if not exists else update it in Netezza

问题描述

在Netezza中使用if not exist语句时出现问题.我试图先检查记录是否不存在，然后再插入否则更新记录.我在Netezza找不到更好的方法.这是我所拥有的但不起作用:

I am having issue using if not exists statement in Netezza. I am trying to check if record is not there first then insert else update the record. I could not find a better way to do this in Netezza. Here is what I have but does not work:

IF NOT EXISTS(SELECT ID FROM OLD_TABLE WHERE ID NOT IN (SELECT ID FROM NEW TABLE ) )
INSERT INTO NEW_TABLE (NAME, LOCATION)
ELSE 
UPDATE NEW_TABLE
SET NAME = X.NAME
FROM (SELECT NAME, LOCATION FROM OLD_TABLE)AS X
WHERE X.NAME = NEW_TABLE.NAME

推荐答案

假设您要提供一个ID，并希望使用old_table中的值对new_table中的一行进行UPSERT.我正在使用修改数据的CTE ，需要Postgres 9.1或更高版本:

Assuming you are providing an ID and want to UPSERT a single row in new_table with values from old_table. I am using a data-modifying CTE, which requires Postgres 9.1 or later:

WITH input AS (SELECT 123 AS id)
, upd AS (
   UPDATE new_table n
   SET    name = o.name
        , location = o.location
   FROM   old_table o, input i
   WHERE  n.id = o.id
   AND    n.id = i.id
   RETURNING id
   )
INSERT INTO new_table (id, name, location)
SELECT o.id, o.name, o.location
FROM   old_table o, input i 
WHERE  NOT EXISTS (SELECT 1 FROM upd)
AND    o.id = i.id;

这是一个密切相关的答案，有更多解释:
有关PostgreSQL更新查询的问题

Here is a closely related answer with more explanation:
Problems with a PostgreSQL upsert query

出现种族状况的机会很小.如果您打算在繁重的并发负载下使用此功能，请考虑以下相关问题以及更多链接:
通过事务进行更新

There is a tiny chance for a race condition. If you are planning to use this under heavy concurrent load, consider this related question and the links for more:
Upsert with a transaction

要修改整个表格:

WITH upd AS (
   UPDATE new_table n
   SET    name = o.name
        , location = o.location
   FROM   old_table o
   WHERE  n.id = o.id
   RETURNING id
   )
INSERT INTO new_table (id, name, location)
SELECT o.id, o.name, o.location
FROM   old_table o
LEFT   JOIN upd u USING (id)
WHERE  u.id IS NULL;

这篇关于如果不存在则插入，否则在Netezza中对其进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！