Insert if not exists else update it in Netezza

Question

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

First you check for `ID`, then you connect via `NAME`. And you only want to update `NAME`, not `LOCATION`? Please clarify. Also, do you want to upsert all rows from old_table or just a particular one? Which one? — Erwin Brandstetter, Mar 17 '14 at 16:35
i want to update both Name and Location. I only want to insert into the New_Table the ID's that are present in Old_Table but not in the New_Table. I hope that clarifies. thanks — moe, Mar 17 '14 at 16:51
Yes, I think I see what you are after. Please clarify the question accordingly. — Erwin Brandstetter, Mar 17 '14 at 16:57

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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;

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

Whole table

To upsert the whole table:

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;

Insert if not exists else update it in Netezza

1 Answers1

Whole table

Linked