> > > 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