I use the query below to identify any records that have a match between the table and view. The query works fine but how can I create an update query update the matching records in dbo.VAC_tbl_Requests_NIL?
I would like to set the fields:
CancelID = 1
CanceledDateTime = Now(), or the SQL equiviilent
CanceledBy = 'Auto Routine'
but only for those records that have a matching NIL_ID in the query below.
SELECT dbo.VAC_tbl_Requests_NIL.CancelID, dbo.VAC_tbl_Requests_NIL.CanceledDateTime, dbo.VAC_tbl_Requests_NIL.CanceledBy
FROM dbo.VAC_tbl_Requests_NIL INNER JOIN
dbo.VAC_View_Requests_ToCancel_DayOff_NIL ON dbo.VAC_tbl_Requests_NIL.AbsenceID = dbo.VAC_View_Requests_ToCancel_DayOff_NIL.AbsenceID
WHERE (dbo.VAC_tbl_Requests_NIL.CancelID IS NULL)
Something like this should do it (and notice how aliasing table names makes things more readable):
UPDATE req
SET CancelID = 1,
CanceledDateTime = GETDATE(),
CanceledBy = 'Auto Routine'
FROM dbo.VAC_tbl_Requests_NIL req
JOIN dbo.VAC_View_Requests_ToCancel_DayOff_NIL dayoff
ON req.AbsenceID = dayoff.AbsenceID
WHERE req.CancelID IS NULL;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.