CODEWITHSUNDEEP
sql-serverdatabasestored-procedures
Michael Hartmann
Michael Hartmann

Reputation: 584

SQL Server : selecting TOP Records and updating results in another table

This is rather complicated to explain in a title.

I have two tables, one that populates web visits by a user, the other is the user table that has a field that stores the last login date.

I want to base the last login date on the latest user visit by each user (if they are registered in the user table.

This will be user in a stored procedure.

Note: I have abbreviated the tables to the relevant columns.

TABLE [Login]

   [User] [VARCHAR](15) NOT NULL,
   [LogOn_Date] [DATETIME] NOT NULL

AND

TABLE [Users]

   [User] [varchar](15) NOT NULL,
   [Last_Login_Dt] [datetime] NULL

I am looking to select the latest date [LogOn_Date] from each user in the Login table, then update the Users table with the [LogOn_Date] value in the [Last_Login_Dt] column that matches users.

Note: not all users that log on are in the Users table, I track everyone via windows authentication regardless if they are a user or not, but only want to update each user that is present in the users table.

Thank you!

Upvotes: 0

Views: 63

Answers (4)

SQLChao
SQLChao

Reputation: 7847

UPDATE u
SET u.Last_Login_Dt = l.LogOn_Date
FROM Users u
JOIN (SELECT *,
      ROW_NUMBER() OVER (PARTITION BY User, ORDER BY LogOn_Date DESC) AS rn
      FROM Login) l ON u.user = l.user and l.rn = 1

The derived table ranks the users logins by date. Join it back to the users but only pull rank 1 which is the most recent login and update the users table

Upvotes: 0

Joe C
Joe C

Reputation: 3993

I would recommend that you do not have a last login field on the users table. Unless you are having some performance concerns, in which case I would suggest an indexed view.

The view would look like 

Select
   U.User, Max(L.LogOnDate)
   From Users U
   Join Login L On L.User = U.User

If you really want to persist this data in the users table I would suggest that you use the above Sql statement as the basis for your work. There are a number of ways to update the users table. The best might be an update statement with a join.

http://www.tech-recipes.com/rx/56823/delete-update-rows-using-inner-join-sql-server/

Upvotes: 0

Robert 30005
Robert 30005

Reputation: 1

update [Users]
set [Last_Login_Dt] = a.Last_Login_Dt
from 
    (select [User], max([Login_date]) as Last_Login_Dt 
     from Login 
     group by [User]) a 
join [SampleDb].[dbo].[Users] b on a.[User] = b.[User]

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

UPDATE u
SET u.Last_Login_Dt = l.LogOn_Date
FROM Users u
JOIN (SELECT 
      [User],
      max(LogOn_Date) AS LogOn_Date
      FROM Login
      GROUP BY [User]) l ON u.[User]= l.[User]

Upvotes: 1

Related Questions