SQL - updating records based on the latest date

Question

I'm having difficulty updating records in a database based on the most recent date, and I'm looking for some recommendations. By the way, I'm new to SQL.

As a background, I have a Windows Forms application with SQL Express and I use ADO.NET to interact with the database. The application is designed so that the user can track the attendance of employees at various courses that should be attended on a periodic basis (for example, every 6 months, every year, etc.). For example, they can select data to see the last time employees attended a given course, and also update the dates of attendance if the employee recently completed the course.

I have three data tables:

• EmployeeDetailsTable - a simple list of employee names, email address, etc., each with a unique identifier
• CourseDetailsTable - A simple list of courses, each with a unique identifier (for example, 1, 2, 3, etc.).
• AttendanceRecordsTable - has 3 columns {EmployeeID, CourseID, AttendanceDate, Comments}

For any given course, the employee will have a history of attendance, that is, if the course should be attended every year, then they will have one record for as many years as they were in the company.

What I want to do is update the Comments field for this employee and this course based on the last visit date. What is the “correct” SQL syntax for this?

I tried a lot of things (like below), but can't make it work:

UPDATE AttendanceRecordsTable
SET Comments = @Comments
WHERE AttendanceRecordsTable.EmployeeID = (SELECT EmployeeDetailsTable.EmployeeID FROM EmployeeDetailsTable WHERE (EmployeeDetailsTable.LastName =@ParameterLastName AND EmployeeDetailsTable.FirstName =@ParameterFirstName)
AND AttendanceRecordsTable.CourseID = (SELECT CourseDetailsTable.CourseID FROM CourseDetailsTable WHERE CourseDetailsTable.CourseName =@CourseName))
GROUP BY MAX(AttendanceRecordsTable.LastDate)

, MAX , GROUP BY. HAVING, .

- ? "" ?

Answer 1

, AttendantsRecordsTable CourseDetailsTable ?

UPDATE 
  dbo.AttendanceRecordsTable
SET 
  Comments = @Comments
FROM
  CourseDetailsTable cd 
INNER JOIN
  Employee e ON e.EmployeeID = AttendanceRecordTable.EmployeeID
WHERE 
  e.LastName = @LastName 
  AND e.FirstName = @FirstName
  AND cd.CourseName = @CourseName
  AND AttendanceRecordsTable.CourseID = cd.CourseID
  AND AttendanceRecordsTable.LastDate = 
        (SELECT MAX(LastDate) 
           FROM AttendanceRecordsTable a
          WHERE a.EmployeeID = e.EmployeeID 
            AND a.CourseID = cd.CourseID)

, - .

AttendanceRecordTable, , Employee CourseDetailsTable. , , AttendanceRecordTable, , , MAX (LastDate) .

:

(SELECT MAX(LastDate) 
   FROM AttendanceRecordsTable a
  WHERE a.EmployeeID = e.EmployeeID AND a.CourseID = cd.CourseID)

MAX () LastDate AttendanceRecordsTable (e.EmployeeID) (cd.CourseID).

, (, , , John Miller !). , - .

Answer 2

, AttendanceRecordsTable id:

UPDATE AttendanceRecordsTable SET Comments = @Comments
WHERE AttendanceRecordsTable.id = (
    SELECT AttendanceRecordsTable.id
        FROM EmployeeDetailsTable 
        JOIN AttendanceRecordsTable ON AttendanceRecordsTable.EmployeeID = EmployeeDetailsTable.EmployeeID·
        JOIN CourseDetailsTable ON AttendanceRecordsTable.CourseID = CourseDetailsTable.CourseID
    WHERE
        EmployeeDetailsTable.LastName =@ParameterLastName AND EmployeeDetailsTable.FirstName =@ParameterFirstName AND
        CourseDetailsTable.CourseName =@CourseName
    ORDER BY AttendanceRecordsTable.LastDate DESC LIMIT 1)

, , coursedetail, , , , . , -.

: , AttendanceRecordsTable. .