max date in linking 2 tables for update

[timc]

I have a table called ppd2002, which has an id field a testdate field (datetime), and a previoustesdate field (datetime). The testdate field is filled in. I have another table called ppd which has an id field and a testdate field. I need to update the ppd2002 field called previoustestdate with the max(testdate) field from ppd, but that max date must be less than the current testdate in the ppd2002 field. The id field is the link field between the 2 tables. Tried to create a view, then a update but still stuck.

[andi_g69]

This view should give you the maximum testdate per id from ppd:

CREATE VIEW getMAXtetdateperID
SELECT ppd.id, MAX (ppd.testdate) maxtestdate
FROM ppd
JOIN ppd2002 on ppd.id = ppd2002.id
where ppd.testdate < ppd2002.testdate

This is how to use the view in an UPDATE:

UPDATE ppd2002
set previoustesdate = ppdmax.maxtestdate
from ppd2002
join getMAXtetdateperID ppdmax on
ppd2002.id = ppdmax.id

Caution: I have not tested this query...

[tonywilko]

What's the relationship between the two tables?
You mention the id field is the link but is this in the form of a foreign key in one of the tables or simply a direct mapping between the two id fields. If it's a case of direct mapping, what is the cardinality of the relationship?