Search code examples
mysqlsqldenormalization

SQL - Syncing Denormalize Table

I have a USERS table and a LOGS table. The LOGS table was normalized, storing only a user_id until recently, when I decided to denormalized it for performance reasons. After this change, I have username and user_role columns as part of LOGS table as well.

Now I need to update existing records of the LOGS table to fill out username and user_role columns, based on the value of user_id column, so that data becomes consistent. How can go about achieving this? I'm looking for possibly an SQL script that I can run on the database server.

Asim

Solution

  • Use something like this. You can just join the users table

    UPDATE LOGS l
INNER JOIN users u ON u.id=l.user_id
SET l.username=u.name,
  l.user_role=u.role
WHERE ... if necessary

    UPDATE:

    UPDATE LOGS l
INNER JOIN (select l.id as log_id, u.*
            from logs l join users u ON u.id=l.user_id
            order by l.id
            limit 10) sub ON sub.id=l.id
SET l.username=sub.name,
  l.user_role=sub.role