sql - 如果不存在则插入，否则在 Netezza 中更新它

Question

我在 Netezza 中使用 if not exists 语句时遇到问题。我试图先检查记录是否不存在然后插入其他更新记录。我找不到在 Netezza 中执行此操作的更好方法。这是我有但不起作用的:

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

Answer 1

假设您提供了一个 ID 并希望使用 old_table 中的值对 new_table 中的单行进行 UPSERT。我正在使用 data-modifying CTE ，这需要 Postgres 9.1 或更高版本:

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;

这是一个密切相关的答案，并有更多解释:
Problems with a PostgreSQL upsert query

出现竞争条件的可能性很小。如果您计划在高并发负载下使用它，请考虑这个相关问题和更多链接:
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;