How to update values in Postgres table based on a match in an array

Question

I need to update some values in a table in my PostgresQL database.

I want to update the stage column, where stage is "Accepted" and the column workspace has a value in an array.

I am trying to select the IDs of specific workspaces into an array, which I can use in my update statement.

I have played around with different things, and this is currently what I have.

DO $$
DECLARE external_app_workspaces INTEGER[];
BEGIN
  SELECT ARRAY(
      SELECT id
      FROM workspace w
      WHERE w."isUsingExternalApp" = true
    )
  INTO external_app_workspaces;


  UPDATE task t
  SET "stage" = 'Licence Issued'
  WHERE "stage" = 'Accepted'
  AND t.workspace IN external_app_workspaces;
END $$;

Running this gives me the error:

Error occurred during SQL query execution

Reason:
SQL Error [42601]: ERROR: syntax error at or near "external_app_workspaces"
  Position: 292

What am I doing wrong?

Answer 1

I think you can resolve this, just in one sentence

UPDATE task 
  SET stage = 'Licence Issued'
  WHERE stage = 'Accepted'
  AND workspace IN 
 (SELECT id FROM workspace WHERE "isUsingExternalApp" = true)

Greetings, Alejandro