How to update a table using a Select statement in the Where Clause
-
I am trying to update a record in an Employee work hours table to record the employee time out. I need to find the last time the employee clocked in then assosciate the time out with the record. thus having a time in and timeout data. UPDATE dbo.EmpWorkHours SET Emp_Timeout = getdate() where Emp_Timeout in (SELECT TOP 1 Emp_timeout FROM dbo.EmpWorkHours WHERE FK_Emp_Login_ID = 'MDoe74' and Emp_Timeout IS NULL ORDER BY RecordNum DESC ) The script looks like it should be doing what I want it to do but it never finds any record, thought the record exist. Any assistance will be great appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
-
I am trying to update a record in an Employee work hours table to record the employee time out. I need to find the last time the employee clocked in then assosciate the time out with the record. thus having a time in and timeout data. UPDATE dbo.EmpWorkHours SET Emp_Timeout = getdate() where Emp_Timeout in (SELECT TOP 1 Emp_timeout FROM dbo.EmpWorkHours WHERE FK_Emp_Login_ID = 'MDoe74' and Emp_Timeout IS NULL ORDER BY RecordNum DESC ) The script looks like it should be doing what I want it to do but it never finds any record, thought the record exist. Any assistance will be great appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
hi Check whether the field Emp_Timeout is null or ' '(Space) in the table dbo.EmpWorkHours Regards Joe -- modified at 1:50 Friday 12th October, 2007
-
I am trying to update a record in an Employee work hours table to record the employee time out. I need to find the last time the employee clocked in then assosciate the time out with the record. thus having a time in and timeout data. UPDATE dbo.EmpWorkHours SET Emp_Timeout = getdate() where Emp_Timeout in (SELECT TOP 1 Emp_timeout FROM dbo.EmpWorkHours WHERE FK_Emp_Login_ID = 'MDoe74' and Emp_Timeout IS NULL ORDER BY RecordNum DESC ) The script looks like it should be doing what I want it to do but it never finds any record, thought the record exist. Any assistance will be great appreciated.
Skan If you knew it would not compile why didn't you tell me?!?!?!
Thanks for your response. I figured it out, using the code below. UPDATE dbo.EmpWorkHours SET Emp_Timeout = getdate() where RecordNum = (SELECT TOP 1 RecordNum FROM dbo.EmpWorkHours WHERE FK_Emp_Login_ID = 'MDoe74' and Emp_Timeout IS NULL ORDER BY RecordNum DESC ) What was wrong with it you may ask? Well, I was selecting the Emp_timeout field which was null. I was then telling to update a record where getdate() equals the value in the Emp_Timeout field. That would never happen because at the time of the query Emp_timeout field is NULL thus it will never equal getdate(). Hope this helps.
Skan If you knew it would not compile why didn't you tell me?!?!?!