CODEWITHSUNDEEP
sqlpostgresqlsql-updateaggregate-functions
James
James

Reputation: 16349

Workaround for using aggregate function in WHERE for an UPDATE statement

I am adding a new feature to a project that uses a PostgreSQL database.

There is a table in the database called brew_sessions - this has a column called condition_date.

brew_sessions is connected to a recipes table through brew_sessions.recipe_id = recipes.id. recipes is then connected to another table called recipe_fermentation_steps through recipes.id = recipe_fermentation_steps.recipe_id.

I am wanting to run an update statement to set the status field on the brew_sessions field to 30 when the condition_date is less than or equal to a calculated number of days from the condition_date value.

This value is the sum of recipe_fermentation_steps.time.

For example:

SELECT
  brew_sessions.id,
  SUM(recipe_fermentation_steps.time)
FROM brew_sessions
  INNER JOIN recipes ON brew_sessions.recipe_id = recipes.id
  INNER JOIN recipe_fermentation_steps ON recipes.id = recipe_fermentation_steps.recipe_id
GROUP BY brew_sessions.id

This has led me to the below query:

UPDATE brew_sessions
SET status = 30 FROM recipes
  INNER JOIN recipe_fermentation_steps ON recipes.id = recipe_fermentation_steps.recipe_id
WHERE brew_sessions.recipe_id = recipes.id
AND brew_sessions.condition_date
    <= CURRENT_TIMESTAMP - INTERVAL '1 day' * SUM(recipe_fermentation_steps.time)

However, this doesn't run as you cannot use an aggregate function in a WHERE.

How do I write the above correctly?

Upvotes: 1

Views: 240

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658072

You can use a subquery. Like:

UPDATE brew_sessions b
SET    status = 30
FROM  (
   SELECT recipe_id, SUM(time) AS sum_time
   FROM   recipe_fermentation_steps
   GROUP  BY 1
   ) f
WHERE  b.recipe_id = f.recipe_id
AND    b.condition_date <= CURRENT_TIMESTAMP - INTERVAL '1 day' * f.sum_time;

If referential integrity is enforced with FK constraints, you do not need to involve the table recipes at all.

The approach is questionable, though. Typically, you do not write functionally dependent values or values depending on the current time into tables. Use a VIEW (or MATERIALIZED VIEW) or similar for that.

Upvotes: 1

Related Questions