Correct sql (arel) for this query?

Question

I have two tables, one with users, the other with profiles. Each table has a created_at column, where I want to update profiles.created_at so that it is the same as the corresponding row (determined by user_id) in users.created_at.

In Rails, I might do something like:

Profile.update_all(created_at: {???})

Trying to create an efficient query as this is being performed on over a million rows.

score 1 · Accepted Answer · answered Aug 06 '13 at 16:32

1

update  profiles p 
set     p.created_at = (select  u.created_at 
                        from    users u 
                        where   u.user_id = p.user_id 
                        and     u.created_at <> p.created_at
                        )

answered Aug 06 '13 at 16:32

Declan_K

6,726
2
19
30

Thanks! Any reason for `created_at <> created_at` clause? – Damien Roche Aug 06 '13 at 17:38
I included that clause to avoid writing to records where the created_at fields already match. (Why take cycles rewriting data that is already correct). – Declan_K Aug 06 '13 at 17:42

Correct sql (arel) for this query?

1 Answers1