2

In my Postgresql database, I am trying to update a column in one table based on data in that table and another table. In my users table, I'm trying to update the new_date column by adding data in a separate date column to an integer column in months from my plans table (e.g. new_date = 2022-02-27 + 5 months); if this was a single table I could add date + interval '1 month' * months, such as something like - UPDATE users SET new_date = date + interval '1 month' * months; However, I am unable to figure out how to do a similar update using a join table between users and plans, with subqueries or common table expressions.

users table (abbreviated): new_date to be updated based on date + months

date new_date
2022-05-21 null
2022-04-15 null

plans table (abbreviated)

months
5
1

join table: SELECT users.date, plans.months, users.new_date FROM users JOIN plans ON users.plan_id = plans.id

date months new_date
2022-05-21 5
2022-04-15 1

Result:

updated users table:

date new_date
2022-05-21 2022-10-21
2022-04-15 2022-05-15
forpas
  • 160,666
  • 10
  • 38
  • 76
user3291025
  • 997
  • 13
  • 20

1 Answers1

2

You can join the tables and multiply the value of the column months by INTERVAL '1 month' in the UPDATE statement:

UPDATE users AS u
SET new_date = u.date + INTERVAL '1 month' * p.months
FROM plans AS p
WHERE p.id = u.plan_id;

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Great, thanks: final code without the "pseudo" structure of the tables: ```UPDATE users AS u SET subscription_active_until = u.confirmed_at + INTERVAL '1 month' * p.months + INTERVAL '3 days' FROM plans AS p WHERE p.id = u.plan_id;``` – user3291025 Jun 01 '22 at 13:56