Question

Photo of Shawn Ross

0

Updating records schedule using SQL

1

We configured our Christmas Eve check-in to use a different schedule. However, I missed one schedule exception, and consequently we have a few people that checked in using an incorrect schedule.

I used the following SQL to identify attendance records that used the wrong ScheduleID:

select [Id], [ScheduleId], [CreatedDateTime]
FROM [dbo].[Attendance]
WHERE [ScheduleId] = '3' AND [CreatedDateTime] >= Convert(datetime, '2017-12-22' )
ORDER BY [CreatedDateTime] DESC;

I would like to change the ScheduleId of those records only to use the ScheduleId of 946. My assumption is that I need to use a form of the following query, but I'm not sure if I have the proper 'limiters' in place to make sure only the records created after 2017-12-22 are updated

Update [dbo].[Attendance]
Set [ScheduleId] = 946
Where [ScheduleId] = 3 AND [CreatedDateTime] >= Convert(datetime, '2017-12-22' );

Thanks for any help!

  • Photo of Daniel Hazelbaker

    1

    Looks good to me. That is usually how I go about things, create a SELECT statement first with the WHERE clause to verify the set of records I will update and then swap out the "SELECT" for an "UPDATE".

    FYI SQL will automatically convert to datetime, so you can actually just do WHERE ... [CreatedDateTime] >= '2017-12-22'