It did the trick, thanks a million, you guys are the best!
Thanks again.

--- In firebird-support@yahoogroups.com, Stefan Heymann <lists@...> wrote:
>
> What about this one:
>
> update TABLE_A a
> set a.description= (select b.DESCRIPTION from TABLE_B b where
b.code = a.code)
> where
> a.SECONDFIELD='1'
>
> So you restrict updates to only those rows where secondfield is '1'.
>
> In your update clause *all* records from A get updated because the
> UPDATE itself does not have a WHERE clause.
>
> Regards
>
> Stefan
>
>
>
> > Dear Folks
> > need help urgently on an update statement
> > This is the setup:
>
> > TABLE_A has lets say 100 rows
> > lets say TABLE_A it has 3 columns, CODE, DESCRIPTION and SECONDFIELD,
> > (all varchars).
>
> > TABLE_B has two columns, CODE, DESCRIPTION (both varchars)
>
> > I want to update TABLE_A.DESCRIPTION, with the value
TABLE_B.DESCRIPTION
> > joined by field 'CODE' which is common in both.
> > However, I want to affect only the records of target TABLE_A, where
> > SECONDFIELD, has a scalar condition lets say '1' In other words
> > something like that
>
> > update TABLE_A a set a.description= (select b.DESCRIPTION from
> > TABLE_B b where a.SECONDFIELD='1' and a.CODE=b.CODE)
>
> > Now, target TABLE_A has lets say 50 records where SECONDFIELD='1' and
> > 50 more where SECONDFIELD='2'
>
> > the statement is ok as far as syntax and it executes. If i execute
> > this, it does the following:
>
> > It says that ALL 100 records were affected.(Why?)
>
> > The 50 records where SECONDFIELD='1' get updated alright, the other 50
> > where
> > SECONDFIELD='2' become null.
>
> > If I execute the opposite
>
> > update TABLE_A a set a.description= (select b.DESCRIPTION from
> > TABLE_B b where a.SECONDFIELD='2' and a.CODE=b.CODE)
>
> > you guessed it, the 50 records where SECONDFIELD='2' get updated
> > alright, the other 50 where
> > SECONDFIELD='1' become null and lose their original values.
> > This statement should have updated (or affected) only the records
> > where the join is succesfull, in this case only 50 of them in either
> > case based on the 'where a.SECONDFIELD=...' clause, and not all,
> > (At least this is what happens in Oracle)
> > why does this happen?
>
> > I know I miss something, any quick ideas?
> > Thanks in advance
>
>
>
> --
> Stefan Heymann
> www.destructor.de/firebird
>