2

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)?

5

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)
  • I edited your answer to the working version, but I'll accept as it was just synthax short of the right solution. many thanks – Gandalf StormCrow Mar 3 '14 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.