At 12:51 PM 2/03/2005 +0000, you wrote:

>Good Morning!
>
>I am attempting to update a table (widgets). My WHERE clause uses an
>inner join to reference corresponding values in a second table
>(records).
>
>The update statement is as follows:
>
>update widgets
>set valid = "Y"
>where (widgets.value_1 - widgets.value_2) * 700 >= records.value_3
>* .12
>and widgets.inventory_id = records.inventory_id
>
>The statement executes correctly against SYBASE and other databases,
>but I receive the following error under Firebird 1.5 (Dialect 1):
>
>Dynamic SQL Error.
>SQL error code = -206.
>Column unknown.
>records.value_3
>At line 3, column 48.
>
>What is the correct syntax for this update under Firebird/InterBase?

Conventional:

update widgets w
set w.valid = 'Y' /* note the single quotes!! */
where exists
(select 1 from records r
where
w.inventory_id = r.inventory_id
and
(w.value_1 - w.value_2) * 700 >= r.value_3 * .12)

or this might do it:

update widgets w
set w.valid =
case
when ( exists
(select 1 from records r
where
w.inventory_id = r.inventory_id
and
(w.value_1 - w.value_2) * 700 >= r.value_3 * .12)) then 'Y'
else w.valid /* or whatever you want, e.g. null */
end

./hb