complex update query postgres

Question

I've got a bunch of ids from table messages and I want to update the table activities. This is how I imagined my query would work (so you get better idea what I'm trying to accomplish):

update activities
set activities.created_at = messages.created_at,
set activities.updated_at = messages.updated_at
from messages
where messages.id in (1,2,3)
activities.trackable_id in (1,2,3)

In activities, the trackable_id is foreign key to the messages table. So I need to set the right created_at and updated_at time in activities table for some messages.

So basically the activity table is the copy of messages table but only in another format.

How can I update created_at and updated_at in the activities table for some message ids, but using the data from messages table (data being updated_at and created_at)?

Answer 1

You do not seem to join your activities and messages tables in your where clause. The more correct syntax would be:

update activities
set created_at = messages.created_at,
updated_at = messages.updated_at
from messages
where messages.id = activities.trackable_id
  AND messages.id in (1,2,3)