September 12, 2007 at 7:30 pm
Hi all, I have problem trying to update one record where there may be more than one entry for a given date. RID EmpID Reason selected Date 1 2 Good 0 2007-09-10 2 2 Nice 0 2007-08-10 3 1 Good 0 2007-09-10 There are two tables involved selection and employee empid is from employee table and is the fkey. I will like to only update one of the records without specifying the RID. Update on single value ignoring any record with the same empid and date . The date column is datetime. The update will be passing to parameter from a web application. the application will calling the update store procedure declare empid int declare @date datetime Update selection set selected-'1' from selection, employee where selection.empid=@empid and ate=@date">Date=@date srt Jaime E. Maccou |
Edited by - jemacc on 09/12/2007 9:03:23 PM |
September 12, 2007 at 9:55 pm
If I understand correctly you are trying to update a specific employee record for a specific date. You mention that empid is a FK in the selection table to the employee table. I believe the code should be as follows:
declare @empid int
declare @date datetime
UPDATE selection
SET selected = 1
WHERE empid=@empid and Date=@date
The code you have "FROM selection, employee" creates a CROSS JOIN I believe.
September 13, 2007 at 4:59 am
Thank you for taking the time to respond. After reviewing my data I had found that some of the date information was updated and the time portion of the date was missing or duplicated and since more than one row kept the same datetime ,the update statement criteria resulted in one or more rows meeting that criteria therefore more than one row was affected. It is still interesting to know how to update just one of those rows which meet the same criteria.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
