CODEWITHSUNDEEP
postgresqlsql-update
sForSujit
sForSujit

Reputation: 983

Pulling data from one table to another table in postgreSQL

I have 2 tables

1) campaigns(campaign_id(int), last_modified(date))
2) events(resource_id(int), event_time(date))

So I have written a query which pulls the data from events table and push into the campaigns table for single campaign_id(755)

update campaign_items
set last_modified = (
        select event_time
        from events
        where request = '/campaignitem/add'
            and resource_id = 755
        order by event_time desc limit 1
        )
where id = 755

So now I wanted to do the same for all the campaign_id from campaign table

Could you please tell me the solution for it

Upvotes: 0

Views: 36

Answers (1)

Vivek S.
Vivek S.

Reputation: 21995

If I understood correctly following is the update statement you're looking for

I assume that campaign_items.id is related with events.resource_id

update campaign_items
set last_modified = evt.event_time
from (
    select max(event_time) event_time
        ,resource_id
    from events
    where request = '/campaignitem/add'
    group by resource_id
    ) evt
where evt.resource_id = campaign_items.id

Upvotes: 1

Related Questions