'Updating jsonb column in one table based on a column in second table using PostgreSQL
I have a table named clubs and these are some of the relevant columns in this table:
COLUMN_NAME DATA_TYPE IS_NULLABLE
id uuid NO
general_fields jsonb YES
and I have another table named plans and these are some of the relevant columns in this table:
COLUMN_NAME DATA_TYPE IS_NULLABLE
id uuid NO
club_id uuid NO
hide_in_online_application bool YES
A club can have multiple plans.
I want to write a query that updates the boolean type allow_applications as FALSE inside the column general_fields for those clubs in the clubs table that only have plans where hide_in_online_application is TRUE
The query will have something like:
UPDATE clubs
SET general_fields = jsonb_set(general_fields, '{allow_applications}', '"false"')
+ the condition where clubs has no plans
+ the condition where all plans from clubs are hide_in_online_application == true
What would be the best way to achieve this?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
