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 |
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.