Ah, I understand. Thanks for the explanation Ann.

I figured out this solution with a subselect, does this look like a good
idea?

UPDATE
PATIENTRECALL
SET
PATIENTRECALL.RECALLSTATUS = 'rsMail'
WHERE
PATIENTRECALL.RECALLDATE >= :STARTDATE AND
PATIENTRECALL.RECALLDATE <= :ENDDATE AND
PATIENTRECALL.PATIENTID IN (SELECT PATIENT.PATIENTID FROM PATIENT WHERE
INACTIVEPATIENT = 'F') AND
PATIENTRECALL.RECALLSTATUS <> 'rsCancelled' AND
PATIENTRECALL.RECALLSTATUS <> 'rsRecalled'


_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Monday, November 20, 2006 1:45 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Joining table in UPDATE statement?



Anthony Tanas wrote:

> I'm trying to do this:

UPDATE <table>
SET <assignments>
FROM <join tables>
WHERE <conditions>

or

UPDATE <join tables>
SET <assignments>
WHERE <conditions>

Neither of these syntaxes is supported by Firebird or the
SQL standard. The reason is that the statements are not
deterministic and update statement that produce random results
are not looked on favorably by the SQL Standard Committee or
the Firebird group.

For example, suppose you have a database with students and
courses. Each student takes several courses and each course
has several students. A "Registration" is an instance of a
class that corresponds to a set of students and an instructor.

UPDATE Students
SET Mentor = I.Name
FROM Students S
INNER JOIN Registrations T ON S.Id = R.StudentId
INNER JOINT Instructors I ON R.InstructorId = I.Id

The join will produce one name for each course the student takes.
Which one is meant to be the mentor?

The standard update, ugly as it is, requires that each of the
sources for the assignment return a single value.

Regards,

Ann







[Non-text portions of this message have been removed]