Reputation: 983
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
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