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

I need each record in tableA to have the same date as its join record
in tableB (TableA and TableB have a number field which is unique in
each table )

Thanks - Rod
I confirmed this by running the query

--- In ib-support@y..., lester@l... wrote:
> > > > This gives me the error 'Single row subquery produced more
that one
> > > > row' - which makes sense.
> > > >
> > > > Any other ideas ?
> > >
> > > If it gives multiple tableB.datefield entries which do you
> > > want?
> >
> > It depends on your condition tableA.number = tableB.number.
> > I assume that SELECT tableB.datefield FROM tableB
> > where tableA.number = tableB.number)
> > produces just single value and we need MAX as a pseudo function
> > to let IB now that we produce a single value.
>
> I was assuming that because the error said that the subquery
> produced more than one line, that a further refinement was
> needed to reduce it to one line.
>
> I use this sort of sub-select all the time to select the
> last update date from a transaction table that has several
> 'tableB' entries for each matching 'tableA' entry.
>
> So what is wrong with?
>
> update tableA
> set tableA.datefield =
> ( SELECT MAX(tableB.datefield) FROM tableB
> where tableA.number = tableB.number)
>
> Other than it may not be the last date that is required.
>
> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services