Question 256375 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – update a column value using a subquery

Probably a very simple solution but I am pretty new to PostgreSQL and I need you help good people. I have a table called countries containing information about different countries and later on I added a column called three_rivers that is of a type BOOL with FALSE as a default value.
ALTER TABLE countries ADD COLUMN three_rivers BOOL DEFAULT FALSE which is fine.
I want to update the value for all the countries that have more than 3 rivers flowing in their territory. I have another table called rivers that contain information about the rivers and a helping table called countries_rivers that ensures the connection between the original tables countries and rivers. In this countries_rivers I have two columns called river_id that countries_rivers.river_id = rivers.id and countries_rivers.country_code = countries.country_code.
This is the query that selects properly the result I want – all countries that have more than 3 rivers:
SELECT country_code, COUNT(country_code) AS counter FROM countries_rivers GROUP BY country_code HAVING COUNT(country_code)>3 ORDER BY counter DESC. This is working fine, too.

My question is how can I update the countries.three_rivers column based on the result of the counter using subquery. Most probably sth like UPDATE countries SET three_rivers=1 WHERE...

Thank you in advance!

Tags:

2

Answers


  1. 0

    You can use a subquery to update.

    WITH RiverCounts AS (
  SELECT country_code, COUNT(country_code) AS counter
  FROM countries_rivers
  GROUP BY country_code
  HAVING COUNT(country_code) > 3
)
UPDATE countries c
SET three_rivers = TRUE
FROM RiverCounts rc
WHERE c.country_code = rc.country_code;
    Login or Signup to reply.
  2. 0

    The following query should work:

    UPDATE countries
SET three_rivers = TRUE
WHERE country_code IN (
    SELECT country_code
    FROM countries_rivers
    GROUP BY country_code
    HAVING COUNT(*) > 3
);

    The subquery will get the code of countries having 3 rivers and the query will update accordingly.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search