我在使用Netezza中的“如果不存在”语句时有问题。我正在尝试检查是否记录不存在,然后插入其他更新记录。我在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发布于 2014-03-17 16:48:40
假设您提供了一个ID,并希望在new_table中使用来自old_table的值的一个行。我使用的是一个数据修改CTE,它需要Postgres9.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;下面是一个与此密切相关的答案,还有更多的解释:
比赛条件的可能性很小。如果您计划在并发负载较重的情况下使用此方法,请考虑以下相关问题和链接:
整桌
要重新插入整个表:
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;https://stackoverflow.com/questions/22458463
复制相似问题