CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

How update the column value depending on the value of other column by procedure?

In PostgreSQL database I have table called 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                    |

This table has unique key which you can create by next sql statement:

ALTER TABLE RELATIONSHIP ADD CONSTRAINT RELATIONSHIP_UNIQUE_KEY UNIQUE (SURVEY_ID, EMPLOYEE);

Lets say I want to add new 3 record by procedure in RELATIONSHIP table.

CALL creator(
    'd5f9c639-13e6-42c1-9043-30783981724b',
    ARRAY['Mark', 'Bob', 'Kate'],
    ARRAY['Google', 'Google', 'HP']
);

If SURVEY_ID and EMPLOYEE values are unique and there is no such record in the table I want to add that record to the table. For example third record wasn't in table in the beginning. For thats why I add it. In the same time if SURVEY_ID and EMPLOYEE values are not unique and there was such record in the table I don't want to add them. For example first and second records. The problem is that I need to update value of ORGANIZATION_NAME column if status is true. For example first record's status false. For thats why I need to update the value of ORGANIZATION_NAME column from Apple to Google. The second record does not change. How to make this update correctly?

Other word, I want such result finally:

| 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                   |

Right now my procedure looks like this:

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.

The problem is partially solved after adding SELECT DISTINCT as you adviced.

If I call procedure with such values there is no error:

CALL creator(
  '99c89a24-fff2-4cbc-a542-b1e956a352f9',
  ARRAY['Mark', 'Kate', 'Mark'],
  ARRAY['NEXT', 'U12', 'NEXT']
);

The problem is teal the same if I call procedure with such values:

CALL creator(
  '99c89a24-fff2-4cbc-a542-b1e956a352f9',
  ARRAY['Mark', 'Kate', 'Mark'],
  ARRAY['NEXT', 'U12', 'HP']
);

What can be done in this case in your opinion? I think the best way in this case is to set the last value of ORGANIZATION_NAME. In mean would be better to set HP.

Upvotes: 1

Views: 209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270051

You want to change the ON CONFLICT clause:

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;

Note that I also changed the FROM clause to do a parallel unnesting of the arrays. I do not recognize the double FROM syntax used in the query in your question.

For the last problem, I think this version of the query will work:

    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

Upvotes: 2

Related Questions