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