At 01:30 PM 13-02-02 +0000, you wrote:

>Yes - but can you see that each tableA.datefield will have the same
>date ( the max or min date ) of the whole of tableB.

No. It will have max/min/first or whatever datefield of the group of tableB records that is restricted by the WHERE clause, i.e. tableB.number matching the tableA.number that the update is positioned to. When it moves to the next tableA.row, it will subselect the aggregation for the rows where tableB.number=tableA.number.

>I need each record in tableA to have the same date as its join record

There is no join here. It is a scalar sub-select which is failing because there are multiple rows in tableB having the same value for "number" (i.e. it's not scalar).

>in tableB (TableA and TableB have a number field which is unique in
>each table )

Not according to the evidence you have provided so far....

Please trim your replies.

H.

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________