SQL (postgresql) update a date column using an interval from data in a join table

Question

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 · Accepted Answer · 2022-06-01 12:56:23Z

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.

edited Jun 1, 2022 at 12:56

answered Jun 1, 2022 at 12:50

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3291025 Over a year ago

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;

Collectives™ on Stack Overflow

SQL (postgresql) update a date column using an interval from data in a join table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related