Hi there

I am trying to update a field from a table with information from a field within the same table. The tricky bit is that I need to match the fields based on a date which is held on a second table.

Basically in plain language I want to tell the table to copy the amount the customer has paid (Enrolments.cenPaid) to cost total (Enrolments.cenCost) where the first session (Courses.crsFirstSession) is older than 01/04/2008

Any help will be appreciated

The server is a Windows server, Enterprise edition 2003 version 8

Thanks in advance


UPDATE Enrolments
SET cenCost = cenPaid
WHERE (Courses.crsFirstSession < CONVERT(DATETIME, '2008-04-01 00:00:00', 102))
(SELECT Courses.crsFirstSession
FROM Enrolments INNER JOIN Courses ON Enrolments.cenVenue = Courses.crsVenue AND Enrolments.cenGroupCode = Courses.crsGroupCode AND
Enrolments.cenCode = Courses.crsCode AND Enrolments.cenCourseOrSes = Courses.crsCourseOrSes)