Rod:

Use a SP in the server, and use a FOR .... DO loop.

Something like:

FOR SELECT tableB.datefield FROM tableB
where tableA.number = tableB.number DO
PlaceYourUpdateCodeHere

Artur

----- Original Message -----
From: rodbracher
To: ib-support@yahoogroups.com
Sent: Wednesday, February 13, 2002 1:03 PM
Subject: [ib-support] Re: Update using two tables


I think this thread of messages has lost the plot.

I need to update a tables date field with the data from another
tables date field where there join is by a key number.

It is not just for one record, but for all.

Surely using max or min will always use the same date from the second
table ?

Thanks - Rod
(Actually - but this shouldn't effect the query - its where the
update tables date field is null, I then need to copy the second
tables date data into it ).

--- In ib-support@y..., "ibfa2000" <fabrice.aeschbacher@k...> wrote:
> > This gives me the error 'Single row subquery produced more that
one
> > row' - which makes sense.
> >
> > Any other ideas ?
> >
>
> Your sub-query:
>
> SELECT tableB.datefield FROM tableB
> where tableA.number = tableB.number
>
> must not return more than 1 row. So you can:
>
> - Modify the WHERE clause to be more restrictive
> - or select only the first row: SELECT MIN(TableB.datefield)
FROM ...
>
> The other idea is to write a stored procedure.
>
> Fabrice
>
>
> >
> > --- In ib-support@y..., Svein Erling Tysvær
> > <svein.erling.tysvaer@k...> wrote:
> > > Of the top of my head:
> > >
> > > update tableA
> > > set tableA.datefield = (SELECT tableB.datefield FROM tableB
> > > where tableA.number = tableB.number)
> > >
> > > I could be wrong,
> > > Set



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