At 12:26 PM 13-02-02 +0000, you wrote:

>This gives me the error 'Single row subquery produced more that one
>row' - which makes sense.
>
>Any other ideas ?

Svein's statement should work if TableA.number and TableB.number are each unique in their own tables. If they are not, then your pseudocode isn't reasonable either...because there is more than one row in TableB eligible for the match and there is no way to know which row the datefield value is to be taken from.

You are going to have to apply further WHERE criteria to the subselect, or aggregate it, to guarantee that the query can find one and only one eligible row for each row being updated in TableA.

Perhaps you want something like this (latest date? only a guess, might be MIN(), FIRST...):

update tableA
set tableA.datefield = (SELECT MAX(tableB.datefield) FROM tableB
where tableB.number = tableA.number)


H.

>--- 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
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

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