如何根据other列的值按过程更新列值？
如何根据other列的值按过程更新列值？
提问于 2019-06-09 20:05:24
在PostgreSQL数据库中，我有一个名为RELATIONSHIP的表
| SURVEY_ID | EMPLOYEE | ORGANIZATION_NAME | STATUS (default: false) |
|--------------------------------------|----------|-------------------|-------------------------|
| d5f9c639-13e6-42c1-9043-30783981724b | Mark | Apple | false |
| d5f9c639-13e6-42c1-9043-30783981724b | Bob | Apple | true |此表具有唯一键，您可以通过下一条sql语句创建该键：
ALTER TABLE RELATIONSHIP ADD CONSTRAINT RELATIONSHIP_UNIQUE_KEY UNIQUE (SURVEY_ID, EMPLOYEE);假设我想在RELATIONSHIP表中按过程添加新的3条记录。
CALL creator(
'd5f9c639-13e6-42c1-9043-30783981724b',
ARRAY['Mark', 'Bob', 'Kate'],
ARRAY['Google', 'Google', 'HP']
);如果SURVEY_ID和EMPLOYEE值是唯一的，并且表中没有这样的记录，我希望将该记录添加到表中。例如，第三条记录在开始时不在表中。这就是我添加它的原因。同时，如果SURVEY_ID和EMPLOYEE值不是唯一的，并且表中有这样的记录，我不想添加它们。例如第一个和第二个记录。问题是，如果status为true，我需要更新ORGANIZATION_NAME列的值。例如，第一条记录的状态为false。这就是为什么我需要将ORGANIZATION_NAME列的值从苹果更新到谷歌。第二条记录不会更改。如何正确地进行此更新？
换句话说，我最终想要这样的结果：
| SURVEY_ID | EMPLOYEE | ORGANIZATION_NAME | STATUS (default: false) |
|--------------------------------------|----------|-------------------|-------------------------|
| d5f9c639-13e6-42c1-9043-30783981724b | Mark | Google | false |
| d5f9c639-13e6-42c1-9043-30783981724b | Bob | Apple | true |
| d5f9c639-13e6-42c1-9043-30783981724b | Kate | HP | false |现在，我的过程看起来像这样：
CREATE OR REPLACE PROCEDURE creator(SURVEY_IDENTIFIER uuid, EMPLOYEES VARCHAR[], ORGANIZATION_NAMES VARCHAR[]) AS $FUNCTION$
BEGIN
INSERT INTO RELATIONSHIP (SURVEY_ID, EMPLOYEE, ORGANIZATION_NAME)
SELECT
SURVEY_IDENTIFIER SURVEY_ID,
EMPLOYEE FROM UNNEST(ARRAY[EMPLOYEES]) EMPLOYEE,
ORGANIZATION_NAME FROM UNNEST(ARRAY[ORGANIZATION_NAMES]) ORGANIZATION_NAME
ON CONFLICT ON CONSTRAINT RELATIONSHIP_UNIQUE_KEY
DO NOTHING;
END;
$FUNCTION$ LANGUAGE plpgsql;PROBLEM
SQL Error [21000]: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time Tip: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.按照您的建议添加SELECT DISTINCT后，问题部分解决了。
如果我使用这样的值调用procedure，则没有错误：
CALL creator(
'99c89a24-fff2-4cbc-a542-b1e956a352f9',
ARRAY['Mark', 'Kate', 'Mark'],
ARRAY['NEXT', 'U12', 'NEXT']
);如果我用这样的值调用procedure，问题也是一样的：
CALL creator(
'99c89a24-fff2-4cbc-a542-b1e956a352f9',
ARRAY['Mark', 'Kate', 'Mark'],
ARRAY['NEXT', 'U12', 'HP']
);在您看来，在这种情况下可以做些什么？我认为在这种情况下最好的方法是设置ORGANIZATION_NAME的最后一个值。在mean中设置HP会更好。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-09 20:12:25
您想要更改ON CONFLICT子句：
INSERT INTO RELATIONSHIP (SURVEY_ID, EMPLOYEE, ORGANIZATION_NAME)
SELECT SURVEY_IDENTIFIER as SURVEY_ID,
u.EMPLOYEE, u.ORGANIZATION
FROM UNNEST(ARRAY[EMPLOYEES],
ARRAY[ORGANIZATION_NAMES]
) u(EMPLOYEE, ORGANIZATION_NAME)
ON CONFLICT ON CONSTRAINT RELATIONSHIP_UNIQUE_KEY
DO UPDATE SET ORGANIZATION_NAME = EXCLUDED.ORGANIZATION_NAME
WHERE NOT RELATIONSHIP.STATUS;注意，我还更改了FROM子句来并行取消数组的嵌套。我不认识您问题中的查询中使用的双FROM语法。
对于最后一个问题，我认为这个版本的查询会起作用：
SELECT DISTINCT ON (u.EMPLOYEE) SURVEY_IDENTIFIER as SURVEY_ID,
u.EMPLOYEE, u.ORGANIZATION
FROM UNNEST(ARRAY[EMPLOYEES],
ARRAY[ORGANIZATION_NAMES]
) WITH ORDINALITY u(EMPLOYEE, ORGANIZATION_NAME, n)
ORDER BY u.EMPLOYEE, n DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56514435
复制相关文章
相似问题