-2

I have a table of users that looks like this:

USERS

userid | email | action | actiontimestamp

And another table of some user actions (login, logout, etc.) that looks like this:

USER_ACTIONS

actionid | userid | action | actiontimestamp

I need to be able to update the USERS table action and actiontimestamp values with a single query where it uses the latest row from the USER_ACTIONS table. Is this possible?

Anshul Sharma
  • 1,018
  • 3
  • 17
  • 39

1 Answers1

0
UPDATE users u
  JOIN ( SELECT DISTINCT
                userid, 
                FIRST_VALUE(action) OVER (PARTITION BY userid ORDER BY actiontimestamp DESC) action,
                MAX(actiontimestamp) OVER (PARTITION BY userid) actiontimestamp 
        ) ua USING (userid)
SET u.action = ua.action,
    u.actiontimestamp = ua.actiontimestamp 

PS. Version 8+ needed. For more ancient versions use single-table UPDATE with 2 correlated queries.

Akina
  • 39,301
  • 5
  • 14
  • 25