Update query required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update query required

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-12-22 : 06:15:07

Hi all,

I am having two tables session and log I need to update the log table logout field when the session table logout field get modified

The two tables having only one common field called User_id

sample datas are here.

session table:

Session_Id session_key User_Id Login_dete Log_out Is_Active IP

1760,a40x5q45wqprlryjkxr3mazm,137,2010-12-22 14:50:23,2010-12-22 15:50:04,N,127.0.0.1
1761,a40x5q45wqprlryjkxr3mazm,137,2010-12-22 15:04:15,2010-12-22 15:50:04,N,127.0.0.1
1762,a40x5q45wqprlryjkxr3mazm,137,2010-12-22 15:28:48,2010-12-22 15:50:04,N,127.0.0.1

Log table:
User_Id login_date user_role Is-Active log_out_time account_reset
137,2010-12-22 14:50:23,NULL,Y,NULL,NULL
137,2010-12-22 15:04:14,NULL,Y,1,NULL,NULL
137,2010-12-22 15:28:14,NULL,Y,1,NULL,NULL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-22 : 06:24:03

update l
set log_out=s.log_out
from log_table as l inner join session_table as s
on l.user_id=s.user_id

Madhivanan

Failing to plan is Planning to fail

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-12-22 : 06:48:40

Thank you sir for your reply ..I tried this but it affects all the rows of user_id ..But i need the the last row of that user must change..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-23 : 03:00:44

What did you mean by last row?

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-12-23 : 04:22:21

This ?


declare @session  table(Session_Id int, User_Id int,Log_out datetime)
insert @session 
select 1760,137,'2010-12-22 15:50:04' union
select 1761,137,'2010-12-22 15:50:04'union
select 1762,137,'2010-12-22 15:50:04'union
select 1762,138,'2010-12-22 15:50:14'union
select 1763,138,'2010-12-22 15:50:14'

declare @Log  table(user_id int, Log_in datetime,Log_out datetime)
insert @Log 

select 137,'2010-12-22 14:50:23',NULL union
select 137,'2010-12-22 15:04:14',NULL union
select 137,'2010-12-22 15:28:14',NULL union
select 138,'2010-12-22 15:28:24',NULL  union
select 138,'2010-12-22 15:28:34',NULL

select * from @Log

update L set L.Log_out=L1.log_out
from @Log L
inner join
(
select max(Session_Id)Session_Id,
       user_id,
       min(log_out)log_out,
       min(Log_in)Log_in
from @session as s
cross apply(select top 1 Log_in  from @Log l where s.user_id=l.user_id order by l.Log_in desc)L2
group by user_id
)L1 on L.Log_in=L1.Log_in and L.user_id=L1.User_Id



select * from @Log

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources