Chaining UPDATE statement to SELECT PostgreSQL-CodePudding

GOAL: To take the results of a SELECT statement and use that dataset to update a boolean field. The use case is a user makes picks for sports teams which are set in an array. The SELECT query should grab the last index of the array (the users latest pick), and if the team selected is listed in the query, flip the isactive status from TRUE to FALSE through update statement. In the example, IN ('a', 'b', ...) would represent the teams selected by the user.

TABLES: USERS

id (uuid) = PK
email (varchar)
username (varchar)
password (hash)
isactive (boolean)

PICKS

userid (uuid) = FK
username (varchar)
picks (array, text)

SELECT QUERY:

select users.username, picks.picks[array_upper(picks, 1)] AS latest_pick
from picks
JOIN users
ON users.username=picks.username
WHERE isactive = true AND picks.picks[array_upper(picks, 1)] IN ('a','b', ...)

What's the best method to take the results of that select and update the isactive status? Sub-query? Temp Table? Thank you for any and all input.

CodePudding user response：

This is the syntax for a join-like UPDATE statement:

UPDATE users AS u
SET isactive = false
FROM picks AS p
WHERE p.username = u.username
  AND u.isactive
  AND p.picks[array_upper(picks, 1)] IN ('a','b', ...);